Making VLOOKUP Range Static

S

schmid

I have an excel file that has about 17 sheets. On three of those
sheets I have VLOOKUP's that look at the other sheets. I load the
other sheets with data and use a macro to format the data how I want
it. During these macros I do add some Columns to the sheets. The last
thing I do is recalucate to get the latest data from the VLOOKUP's.
However, when I add the columns to the sheets it changes the ranges on
my VLOOKUP's. I do not want that to occur. How do I make the range
static? I have tried the following, "$D:$Q", that does not do it.
what do I need to do?
 
S

schmid

Biff said:
Hi!

Try it like this:

=VLOOKUP(A1,INDIRECT("D:Q"),2,0)

Biff
Click to expand...

The VLOOKUP is looking at a range that is on another sheet in the file.
What is the proper syntax to look at another sheet? where in the text
does the sheet name go?

Schmid
 
B

Biff

schmid said:
The VLOOKUP is looking at a range that is on another sheet in the file.
What is the proper syntax to look at another sheet? where in the text
does the sheet name go?

Schmid
Click to expand...

Like this:

=VLOOKUP(A1,INDIRECT("'Sheet_name'!D:Q"),2,0)

Biff
 
Top