Duplicating cells with formula's

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
 
P

Pete_UK

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
 
S

Storm_21_924

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top