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.
---
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" <spam@spamthis.com> wrote in message news:upUh2SxLFHA.3616@TK2MSFTNGP09.phx.gbl...
> 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
>
>

[Joe Murphy]

Hi David,

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

"David McRitchie" <dmcritchie@msn.com> wrote in message
news:eDvF2exLFHA.2252@TK2MSFTNGP15.phx.gbl...
> 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" <spam@spamthis.com> wrote in message
> news:upUh2SxLFHA.3616@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>>
>
>

[Carol Baker]

Thanks, David. Using a named range solved my Vlookup update problem. I used it on a spreadsheet containing Vlookups that I'd used for years. I then decided to update the spreadsheet by copying additional worksheets from another spreadsheet and that's when the problem started - It resulted in my VLookups only updating when I saved the spreadsheet. There were no external links. All of the links were within the same worksheet. Changing the Vlookup range to a named range solved the problem - don't know why.

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" <spam@spamthis.com> wrote in message news:upUh2SxLFHA.3616@TK2MSFTNGP09.phx.gbl...
> 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
>
>