Why not just use a dynamic named range?

Biff

"Ragdyer" wrote in message
...
I read the OP a little differently then you do Peo.

I believe that when he said he misses the "top line", he wanted the range
to
*expand* with each row insertion.

This anchors the "top" reference, and allows the range to expand downward:

=VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4"):'Historical
Funds'!$C$3001,2,FALSE)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
One way

=VLOOKUP(A3,INDIRECT("'Historical Funds'!$B$4:$C$3000"),2,FALSE)

will always point to the same range, note that the workbook has to be
open
or else you'll get a ref error

--
Regards,

Peo Sjoblom

(No private emails please)


"dazman" wrote in
message ...

I have a worksheet that grabs stock information via web queries. It
always writes today's information at the top of the page and to do this
it I insert a row using VBA and set the cells to the correct values.

The problem is that I have a number of other worksheets using the data
from this stock information worksheet via lookups. And the insert row
automatically alters their formulas from something like this:

=VLOOKUP(A3,'Historical Funds'!$B$4:$C$3000,2,FALSE)
To this
=VLOOKUP(A3,'Historical Funds'!$B$5:$C$3001,2,FALSE)

Which causes a problem as then all my lookups miss the top line of my
array definitions. Any tips on how I can stop this?


--
dazman
------------------------------------------------------------------------
dazman's Profile:
http://www.excelforum.com/member.php...o&userid=25903
View this thread:
http://www.excelforum.com/showthread...hreadid=393690