Trying to write a formula that changes which worksheet to used

I am trying to write a formula that uses a different worksheet in the current
workbook depending on the value from a lookup result and/or the direct entry
of a worksheet name in one cell.
For instance, if my lookup result is November, I want to pull information
from cells on the November worksheet, if December, information from the
December worksheet, etc.

This way, all I have to do is change the lookup value and/or the entry to
get different results based on the appropriate worksheet. This is useful in
repetitive activities using the same layout but different numbers.

Any help would be appreciated.

Take a look at the INDIRECT function. This allows you to build a reference
from strings of data, meaning you could have a single cell on your summary
sheet to contain the sheet name you wish to pull, and then build a table of
values from references using INDIRECT. Then simply changing the sheet name
in that one cell will bring in the relevant data.

Eg, assuming you had cell A1 containing the Sheet name of whatever sheet you
want to pull, and the values from each sheet were in cells B2:B6, then you
could set up on your summary sheet a set of values like this

=INDIRECT(A1&"!B2")
=INDIRECT(A1&"!B3")
=INDIRECT(A1&"!B4")
=INDIRECT(A1&"!B5")
=INDIRECT(A1&"!B6")

If abc were your sheet name, then it would pull data from sheet abc, or
change
it to def and it will pull from sheet def.

Note the syntax changes slightly if your sheet names have any spaces in
them:-

=INDIRECT("'"&A1&"'!B2")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"JT Spitz" <JT [email protected]> wrote in message
news:[email protected]...
> I am trying to write a formula that uses a different worksheet in the
current
> workbook depending on the value from a lookup result and/or the direct
entry
> of a worksheet name in one cell.
> For instance, if my lookup result is November, I want to pull information
> from cells on the November worksheet, if December, information from the
> December worksheet, etc.
>
> This way, all I have to do is change the lookup value and/or the entry to
> get different results based on the appropriate worksheet. This is useful
in
> repetitive activities using the same layout but different numbers.
>
> Any help would be appreciated.

This worked perfectly.

I was confused by the use of only one " mark before the !, since I was
always under the impression that ! represented the (for lack of a better
term) division between worksheet name and cell, but obviously I have a lot to
learn about excel references in formulas. Couldn't find this out in the help
screens, so your solution has given me a much needed insight into how to
write my formulas.

Best regards.

"Ken Wright" wrote:

> Take a look at the INDIRECT function. This allows you to build a reference
> from strings of data, meaning you could have a single cell on your summary
> sheet to contain the sheet name you wish to pull, and then build a table of
> values from references using INDIRECT. Then simply changing the sheet name
> in that one cell will bring in the relevant data.
>
> Eg, assuming you had cell A1 containing the Sheet name of whatever sheet you
> want to pull, and the values from each sheet were in cells B2:B6, then you
> could set up on your summary sheet a set of values like this
>
> =INDIRECT(A1&"!B2")
> =INDIRECT(A1&"!B3")
> =INDIRECT(A1&"!B4")
> =INDIRECT(A1&"!B5")
> =INDIRECT(A1&"!B6")
>
> If abc were your sheet name, then it would pull data from sheet abc, or
> change
> it to def and it will pull from sheet def.
>
> Note the syntax changes slightly if your sheet names have any spaces in
> them:-
>
> =INDIRECT("'"&A1&"'!B2")
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "JT Spitz" <JT [email protected]> wrote in message
> news:[email protected]...
> > I am trying to write a formula that uses a different worksheet in the
> current
> > workbook depending on the value from a lookup result and/or the direct
> entry
> > of a worksheet name in one cell.
> > For instance, if my lookup result is November, I want to pull information
> > from cells on the November worksheet, if December, information from the
> > December worksheet, etc.
> >
> > This way, all I have to do is change the lookup value and/or the entry to
> > get different results based on the appropriate worksheet. This is useful
> in
> > repetitive activities using the same layout but different numbers.
> >
> > Any help would be appreciated.
>
>
>

There were two of them though, eg:-

=INDIRECT(A1&"!B2")

If I were referencing B2 on Sheet2 then a direct formula would be

=Sheet2!B2

I already have Sheet2 as a string in cell A1, so I need to add to it,
another string, ie !B2

I can get the =Sheet2 but just by using =A1, but to add a string to that i
need to enclose it in quotes, ie "!B2"
such that my formula becomes =A1&"!B2"

I then just use INDIRECT to turn that whole string into a reference.

Note though, that if there is a space in the sheet name, ie Sheet 2 as
opposed to Sheet2, then the whole sheet name needs to be enclosed in single
quotes, eg

='Sheet 2'!B2

which is why you need to add them to the string in the INDIRECT function:-

=INDIRECT("'"&A1&"'!B2")

See there is one before the & and one directly before the !

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"JT Spitz" <[email protected]> wrote in message
news:[email protected]...
> This worked perfectly.
>
> I was confused by the use of only one " mark before the !, since I was
> always under the impression that ! represented the (for lack of a better
> term) division between worksheet name and cell, but obviously I have a lot
to
> learn about excel references in formulas. Couldn't find this out in the
help
> screens, so your solution has given me a much needed insight into how to
> write my formulas.
>
> Best regards.
>
> "Ken Wright" wrote:
>
> > Take a look at the INDIRECT function. This allows you to build a
reference
> > from strings of data, meaning you could have a single cell on your
summary
> > sheet to contain the sheet name you wish to pull, and then build a table
of
> > values from references using INDIRECT. Then simply changing the sheet
name
> > in that one cell will bring in the relevant data.
> >
> > Eg, assuming you had cell A1 containing the Sheet name of whatever sheet
you
> > want to pull, and the values from each sheet were in cells B2:B6, then
you
> > could set up on your summary sheet a set of values like this
> >
> > =INDIRECT(A1&"!B2")
> > =INDIRECT(A1&"!B3")
> > =INDIRECT(A1&"!B4")
> > =INDIRECT(A1&"!B5")
> > =INDIRECT(A1&"!B6")
> >
> > If abc were your sheet name, then it would pull data from sheet abc, or
> > change
> > it to def and it will pull from sheet def.
> >
> > Note the syntax changes slightly if your sheet names have any spaces in
> > them:-
> >
> > =INDIRECT("'"&A1&"'!B2")
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
--
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
--
> >
> > "JT Spitz" <JT [email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to write a formula that uses a different worksheet in the
> > current
> > > workbook depending on the value from a lookup result and/or the direct
> > entry
> > > of a worksheet name in one cell.
> > > For instance, if my lookup result is November, I want to pull
information
> > > from cells on the November worksheet, if December, information from
the
> > > December worksheet, etc.
> > >
> > > This way, all I have to do is change the lookup value and/or the entry
to
> > > get different results based on the appropriate worksheet. This is
useful
> > in
> > > repetitive activities using the same layout but different numbers.
> > >
> > > Any help would be appreciated.
> >
> >
> >

Where were you when our office converted from Lotus to Excel?
Your help and education is very much appreciated.

Best regards. JT

"Ken Wright" wrote:

> There were two of them though, eg:-
>
> =INDIRECT(A1&"!B2")
>
> If I were referencing B2 on Sheet2 then a direct formula would be
>
> =Sheet2!B2
>
> I already have Sheet2 as a string in cell A1, so I need to add to it,
> another string, ie !B2
>
> I can get the =Sheet2 but just by using =A1, but to add a string to that i
> need to enclose it in quotes, ie "!B2"
> such that my formula becomes =A1&"!B2"
>
> I then just use INDIRECT to turn that whole string into a reference.
>
> Note though, that if there is a space in the sheet name, ie Sheet 2 as
> opposed to Sheet2, then the whole sheet name needs to be enclosed in single
> quotes, eg
>
> ='Sheet 2'!B2
>
> which is why you need to add them to the string in the INDIRECT function:-
>
> =INDIRECT("'"&A1&"'!B2")
>
> See there is one before the & and one directly before the !
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "JT Spitz" <[email protected]> wrote in message
> news:[email protected]...
> > This worked perfectly.
> >
> > I was confused by the use of only one " mark before the !, since I was
> > always under the impression that ! represented the (for lack of a better
> > term) division between worksheet name and cell, but obviously I have a lot
> to
> > learn about excel references in formulas. Couldn't find this out in the
> help
> > screens, so your solution has given me a much needed insight into how to
> > write my formulas.
> >
> > Best regards.
> >
> > "Ken Wright" wrote:
> >
> > > Take a look at the INDIRECT function. This allows you to build a
> reference
> > > from strings of data, meaning you could have a single cell on your
> summary
> > > sheet to contain the sheet name you wish to pull, and then build a table
> of
> > > values from references using INDIRECT. Then simply changing the sheet
> name
> > > in that one cell will bring in the relevant data.
> > >
> > > Eg, assuming you had cell A1 containing the Sheet name of whatever sheet
> you
> > > want to pull, and the values from each sheet were in cells B2:B6, then
> you
> > > could set up on your summary sheet a set of values like this
> > >
> > > =INDIRECT(A1&"!B2")
> > > =INDIRECT(A1&"!B3")
> > > =INDIRECT(A1&"!B4")
> > > =INDIRECT(A1&"!B5")
> > > =INDIRECT(A1&"!B6")
> > >
> > > If abc were your sheet name, then it would pull data from sheet abc, or
> > > change
> > > it to def and it will pull from sheet def.
> > >
> > > Note the syntax changes slightly if your sheet names have any spaces in
> > > them:-
> > >
> > > =INDIRECT("'"&A1&"'!B2")
> > >
> > > --
> > > Regards
> > > Ken....................... Microsoft MVP - Excel
> > > Sys Spec - Win XP Pro / XL 97/00/02/03
> > >
> >
> > --------------------------------------------------------------------------
> --
> > > It's easier to beg forgiveness than ask permission :-)
> >
> > --------------------------------------------------------------------------
> --
> > >
> > > "JT Spitz" <JT [email protected]> wrote in message
> > > news:[email protected]...
> > > > I am trying to write a formula that uses a different worksheet in the
> > > current
> > > > workbook depending on the value from a lookup result and/or the direct
> > > entry
> > > > of a worksheet name in one cell.
> > > > For instance, if my lookup result is November, I want to pull
> information
> > > > from cells on the November worksheet, if December, information from
> the
> > > > December worksheet, etc.
> > > >
> > > > This way, all I have to do is change the lookup value and/or the entry
> to
> > > > get different results based on the appropriate worksheet. This is
> useful
> > > in
> > > > repetitive activities using the same layout but different numbers.
> > > >
> > > > Any help would be appreciated.
> > >
> > >
> > >
>
>
>

LOL - You're welcome :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"JT Spitz" <[email protected]> wrote in message
news:[email protected]...
> Where were you when our office converted from Lotus to Excel?
> Your help and education is very much appreciated.
>
> Best regards. JT
>
> "Ken Wright" wrote:
>
> > There were two of them though, eg:-
> >
> > =INDIRECT(A1&"!B2")
> >
> > If I were referencing B2 on Sheet2 then a direct formula would be
> >
> > =Sheet2!B2
> >
> > I already have Sheet2 as a string in cell A1, so I need to add to it,
> > another string, ie !B2
> >
> > I can get the =Sheet2 but just by using =A1, but to add a string to that
i
> > need to enclose it in quotes, ie "!B2"
> > such that my formula becomes =A1&"!B2"
> >
> > I then just use INDIRECT to turn that whole string into a reference.
> >
> > Note though, that if there is a space in the sheet name, ie Sheet 2 as
> > opposed to Sheet2, then the whole sheet name needs to be enclosed in
single
> > quotes, eg
> >
> > ='Sheet 2'!B2
> >
> > which is why you need to add them to the string in the INDIRECT
function:-
> >
> > =INDIRECT("'"&A1&"'!B2")
> >
> > See there is one before the & and one directly before the !
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
--
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
--
> >
> > "JT Spitz" <[email protected]> wrote in message
> > news:[email protected]...
> > > This worked perfectly.
> > >
> > > I was confused by the use of only one " mark before the !, since I was
> > > always under the impression that ! represented the (for lack of a
better
> > > term) division between worksheet name and cell, but obviously I have a
lot
> > to
> > > learn about excel references in formulas. Couldn't find this out in
the
> > help
> > > screens, so your solution has given me a much needed insight into how
to
> > > write my formulas.
> > >
> > > Best regards.
> > >
> > > "Ken Wright" wrote:
> > >
> > > > Take a look at the INDIRECT function. This allows you to build a
> > reference
> > > > from strings of data, meaning you could have a single cell on your
> > summary
> > > > sheet to contain the sheet name you wish to pull, and then build a
table
> > of
> > > > values from references using INDIRECT. Then simply changing the
sheet
> > name
> > > > in that one cell will bring in the relevant data.
> > > >
> > > > Eg, assuming you had cell A1 containing the Sheet name of whatever
sheet
> > you
> > > > want to pull, and the values from each sheet were in cells B2:B6,
then
> > you
> > > > could set up on your summary sheet a set of values like this
> > > >
> > > > =INDIRECT(A1&"!B2")
> > > > =INDIRECT(A1&"!B3")
> > > > =INDIRECT(A1&"!B4")
> > > > =INDIRECT(A1&"!B5")
> > > > =INDIRECT(A1&"!B6")
> > > >
> > > > If abc were your sheet name, then it would pull data from sheet abc,
or
> > > > change
> > > > it to def and it will pull from sheet def.
> > > >
> > > > Note the syntax changes slightly if your sheet names have any spaces
in
> > > > them:-
> > > >
> > > > =INDIRECT("'"&A1&"'!B2")
> > > >
> > > > --
> > > > Regards
> > > > Ken....................... Microsoft MVP - Excel
> > > > Sys Spec - Win XP Pro / XL 97/00/02/03
> > > >
> > >
> >
> --------------------------------------------------------------------------
> > --
> > > > It's easier to beg forgiveness than ask permission
:-)
> > >
> >
> --------------------------------------------------------------------------
> > --
> > > >
> > > > "JT Spitz" <JT [email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I am trying to write a formula that uses a different worksheet in
the
> > > > current
> > > > > workbook depending on the value from a lookup result and/or the
direct
> > > > entry
> > > > > of a worksheet name in one cell.
> > > > > For instance, if my lookup result is November, I want to pull
> > information
> > > > > from cells on the November worksheet, if December, information
from
> > the
> > > > > December worksheet, etc.
> > > > >
> > > > > This way, all I have to do is change the lookup value and/or the
entry
> > to
> > > > > get different results based on the appropriate worksheet. This is
> > useful
> > > > in
> > > > > repetitive activities using the same layout but different numbers.
> > > > >
> > > > > Any help would be appreciated.
> > > >
> > > >
> > > >
> >
> >
> >