VLOOKUP formula not updating automatically

[Joe Murphy]

I have a spreadsheet that pulls data from a SQL database, compares that
data, using VLOOKUP, to an array in another sheet and returns and sums data
on a third sheet. The VLOOKUP command looks like this:


=IF(ISERROR(VLOOKUP('AeXHD
Data'!E2,LookupData!$D$1:$E$110,2,FALSE)),"",VLOOKUP('AeXHD
Data'!E2,LookupData!$D$1:$E$110,2,FALSE))

I've copied this formula down to about 1500 cells. Some of these cells are
empty, since not enough data from the SQL query is available yet, but I
copied it down all 1500 rows so that in the future, when I refresh the data
from SQL, the formula will run and the cells will be populated
automatically. Unfortunately it is not happening. It seems that each time I
refresh, the formula is getting copied over out of order. The VLOOKUP
statement would look at E2, E3, E4, E5 and then jump to, say E10. I can fix
it only by copying down the formula again to all 1500 cells.

I hope I'm making some sense.

How can I get it so that I no longer have to copy down the VLOOKUP formula
each time I refresh the SQL data?

Thanks,
JM

 

[David McRitchie]

Hi Joe,

Are you deleting rows from the sheet with the formulas, that
would explain a jump. Also are the formulas on the same row
as the argument. The formula with "E2" for example -- is it
on row 2.

You can use a named range for the range on the other sheet,
at this point I don't see it making a difference in what is happening.

 

[Joe Murphy]

Hi David,

No, I'm not deleting any rows, just dragging cells.