Trying to write a formula that changes which worksheet to used

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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")
 
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.
 
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 !
 
Where were you when our office converted from Lotus to Excel? :)
Your help and education is very much appreciated.

Best regards. JT
 
Back
Top