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)),"",VLOOK UP('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

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joe Murphy" wrote in message ...
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)),"",VLOOK UP('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

Hi David,

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

"David McRitchie" wrote in message
...
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joe Murphy" wrote in message
...
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)),"",VLOOK UP('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