worksheet tab name as part of a cell reference

I have a file with 50 worksheets, one for each state of the US.

I am making a master worksheet with a VLOOKUP function to pull data from the
other sheets depending on what state name the user types into a certain cell.
A VLOOKUP functin will pull the data, like this:

=VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE)

I'd like to make the " 'Alabama'! " part of the function a link to the cell
where the user enters the state name. In this way, the function would update
itself with whatever state the user wanted to see. However, it seems like
worksheet names are static, not linkable. Is there a way to get ths to work?

One way

=VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FALSE)

where B2 would hold the state (sheet) name
--

Regards,

Peo Sjoblom

"cwee" <[email protected]> wrote in message
news:[email protected]...
> I have a file with 50 worksheets, one for each state of the US.
>
> I am making a master worksheet with a VLOOKUP function to pull data from
the
> other sheets depending on what state name the user types into a certain
cell.
> A VLOOKUP functin will pull the data, like this:
>
> =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE)
>
> I'd like to make the " 'Alabama'! " part of the function a link to the
cell
> where the user enters the state name. In this way, the function would
update
> itself with whatever state the user wanted to see. However, it seems like
> worksheet names are static, not linkable. Is there a way to get ths to
work?

Hi Peo

how does the B$10 work in a VLOOKUP statement?

Cheers
JulieD

"Peo Sjoblom" <[email protected]> wrote in message
news:%[email protected]...
> One way
>
> =VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FALSE)
>
> where B2 would hold the state (sheet) name
> --
>
> Regards,
>
> Peo Sjoblom
>
> "cwee" <[email protected]> wrote in message
> news:[email protected]...
>> I have a file with 50 worksheets, one for each state of the US.
>>
>> I am making a master worksheet with a VLOOKUP function to pull data from
> the
>> other sheets depending on what state name the user types into a certain
> cell.
>> A VLOOKUP functin will pull the data, like this:
>>
>> =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE)
>>
>> I'd like to make the " 'Alabama'! " part of the function a link to the
> cell
>> where the user enters the state name. In this way, the function would
> update
>> itself with whatever state the user wanted to see. However, it seems like
>> worksheet names are static, not linkable. Is there a way to get ths to
> work?
>
>

Hi Julie,

I assumed the OP put the column index in B10, the formula posted was

=VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE)


--

Regards,

Peo Sjoblom

"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Peo
>
> how does the B$10 work in a VLOOKUP statement?
>
> Cheers
> JulieD
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:%[email protected]...
> > One way
> >
> > =VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FALSE)
> >
> > where B2 would hold the state (sheet) name
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "cwee" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a file with 50 worksheets, one for each state of the US.
> >>
> >> I am making a master worksheet with a VLOOKUP function to pull data
from
> > the
> >> other sheets depending on what state name the user types into a certain
> > cell.
> >> A VLOOKUP functin will pull the data, like this:
> >>
> >> =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE)
> >>
> >> I'd like to make the " 'Alabama'! " part of the function a link to the
> > cell
> >> where the user enters the state name. In this way, the function would
> > update
> >> itself with whatever state the user wanted to see. However, it seems
like
> >> worksheet names are static, not linkable. Is there a way to get ths to
> > work?
> >
> >
>
>

Peo, you are a star. worked like a chram. Julie, the B10 just refers to a
column #, which I have as a number at the top of the column.

"JulieD" wrote:

> Hi Peo
>
> how does the B$10 work in a VLOOKUP statement?
>
> Cheers
> JulieD
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:%[email protected]...
> > One way
> >
> > =VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FALSE)
> >
> > where B2 would hold the state (sheet) name
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "cwee" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a file with 50 worksheets, one for each state of the US.
> >>
> >> I am making a master worksheet with a VLOOKUP function to pull data from
> > the
> >> other sheets depending on what state name the user types into a certain
> > cell.
> >> A VLOOKUP functin will pull the data, like this:
> >>
> >> =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE)
> >>
> >> I'd like to make the " 'Alabama'! " part of the function a link to the
> > cell
> >> where the user enters the state name. In this way, the function would
> > update
> >> itself with whatever state the user wanted to see. However, it seems like
> >> worksheet names are static, not linkable. Is there a way to get ths to
> > work?
> >
> >
>
>
>