Making VLOOKUP Range Static

[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?

 

[Biff]

Hi!

Try it like this:

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

Biff

 

[schmid]

Hi!

Try it like this:

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

Biff

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

 

[Biff]

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

Like this:

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

Biff

 

[schmid]

Like this:

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

Biff

Biff

Worked like a charm... Thanks so much bud!

Steve

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff

Worked like a charm... Thanks so much bud!

Steve