Stop Insert Row modifying formula data

[dazman]

I have a worksheet that grabs stock information via web queries. I
always writes today’s information at the top of the page and to do thi
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 dat
from this stock information worksheet via lookups. And the insert ro
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 m
array definitions. Any tips on how I can stop this

 

[Peo Sjoblom]

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)

 

[Ragdyer]

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)

 

[Biff]

Why not just use a dynamic named range?

Biff

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)

 

[Ragdyer]

Probably because it's easier (for me), to suggest a single formula.<bg>

Lot more typing to describe how to create one (dynamic range), and the
results are the same.

Welllllll, almost the same.

The Indirect() formula is a little more robust, taking the possibility of
blank rows into consideration.

 

[dazman]

Thanks for the replies - Sorted now

 

[RagDyer]

I usually say "Thanks For The Feed-back",
BUT ...
Your feed-back is lacking in *exactly* what you used to "get it sorted".<bg>

 

[dazman]

Hi RagDyer

I ended up only needing the syntax to anchor the "top" reference i
this instance because I can change the range with my VBA code. Howeve
after reading about the INDIRECT procedure I have used it a few tim
since in my forulas for this reason.

Thanks!
Da