Pasting formulas to an entire column

[redjay]

Hi all

I have a formula (VLOOKUP) which is working well in cell B1.

I have applied the formula to the entire column by simply using copy and paste special. The formula itself is working fine as I have referenced it correctly, however I now have thousands of rows of invalid cell references, going down from where the data ends in row A160 to row 1048576!

How can I apply the formula to an entire column but ignoring those rows where there are blanks? I cannot simply select a column range as the data I am working on is going to have varying numbers of rows.

I am aware I can use CTRL+END to find the last row but this does not work in my case as entire rows have been pasted into the sheet I am working on.

Thanks in advance for any help

 

[number kruncher]

WOW, that a lot of VLOOKUP's.

I suggest you wrap your formula in an IFERROR statement, eg.

=IFERROR(original formula,"")