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" <[email protected]> wrote in message
news:[email protected]...
>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?
>

[schmid]

Biff wrote:
> Hi!
>
> Try it like this:
>
> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
>
> Biff
>
> "schmid" <[email protected]> wrote in message
> news:[email protected]...
> >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?
> >

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]

"schmid" <[email protected]> wrote in message
news:[email protected]...
>
> Biff wrote:
>> Hi!
>>
>> Try it like this:
>>
>> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
>>
>> Biff
>>
>> "schmid" <[email protected]> wrote in message
>> news:[email protected]...
>> >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?
>> >
>
> 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]

Biff wrote:
> "schmid" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Biff wrote:
> >> Hi!
> >>
> >> Try it like this:
> >>
> >> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
> >>
> >> Biff
> >>
> >> "schmid" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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?
> >> >
> >
> > 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


Biff

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

Steve

[Biff]

You're welcome. Thanks for the feedback!

Biff

"schmid" <[email protected]> wrote in message
news:[email protected]...
>
> Biff wrote:
>> "schmid" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > Biff wrote:
>> >> Hi!
>> >>
>> >> Try it like this:
>> >>
>> >> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
>> >>
>> >> Biff
>> >>
>> >> "schmid" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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?
>> >> >
>> >
>> > 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
>
>
> Biff
>
> Worked like a charm... Thanks so much bud!
>
> Steve
>