Duplicating cells with formula's

  • Thread starter Thread starter Storm_21_924
  • Start date Start date
S

Storm_21_924

I'm havnig a problem that I just can't figure out...
I have the formula....

=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP(A2,Sheet3!
A3:B280,2,FALSE),"")

I need to copy the formula down a column from row 3 - 280 only
changing the A2 after each vlookup to the next corrisponding A3, a4,
a5. etc. When I drag the little plus down it copies it but adjusts it
incorrectly.

Example of how I want it to look....
This formula would go in Column A Starting Row 2
Row 2:=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A2,Sheet3!A3:B280,2,FALSE),"")
Row 3:=IF(ISNUMBER(VLOOKUP(A3,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A3,Sheet3!A3:B280,2,FALSE),"")
Row 4:=IF(ISNUMBER(VLOOKUP(A4,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A4,Sheet3!A3:B280,2,FALSE),"")
etc to Row 260

Example of how it actually looks.....
Row 2:=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A2,Sheet3!A3:B280,2,FALSE),"")
Row 3:=IF(ISNUMBER(VLOOKUP(A3,Sheet3!A4:B281,2,FALSE)),VLOOKUP
(A3,Sheet3!A4:B281,2,FALSE),"")
Row 4:=IF(ISNUMBER(VLOOKUP(A4,Sheet3!A5:B282,2,FALSE)),VLOOKUP
(A4,Sheet3!A5:B282,2,FALSE),"")

I'm tried using the replace tool, but Can't get it right.
Without manually typing it, lots of formulas to retype.

Any help would be greatly appreciated..
Excel 2007


Thanks!
Shaun
 
You need to fix the references to the table so that it does not change
when you copy it down. You do this by putting $ symbols in front of
(in this case) the row references, and in doing so they become
absolute references rather than relative - you can find out more in XL
Help, but your formula would become:

=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A$3:B$280,2,FALSE)),VLOOKUP(A2,Sheet3!A
$3:B$280,2,FALSE),"")

Now when you copy it down the table will not change.

Hope this helps.

Pete
 
Perfect!!! That's exactly what I'm looking for.. I've been racking my
brain, completely forgot about absolutes vs relatives.

Thanks for you help!
 
Back
Top