Substituting text in a cell for a worksheet name in a formula

**jmoffett** · 02-19-2010, 12:37 AM

The content of cell "animal!A1" will change according to a simple vlookup table. Let's say the value can be "dog", "cat", or "horse". In cell "animal!A5", I want to duplicate the content either "dog!A5", "cat!A5", or "horse!A5", depending on the current value of "animal!A1".

I've tried to do a simple reference like:

="A1"!A5

or

=A1!A5

wanting the A1 to actually read either dog, cat, or horse so the reference would refer to the worksheet of the same names. This doesn't work, so I need to know if there is a way to do this. Thanks.

**ratcat** · 02-19-2010, 03:31 AM

G'day

Try this

=INDIRECT(A1&"!A5")

**DonkeyOte** · 02-19-2010, 03:50 AM

It's generally a good idea when using INDIRECT to encase the sheet reference within '

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

this will account for sheet names that include spaces etc...

**ratcat** · 02-19-2010, 03:55 AM

O yeah good pick up DO

**jmoffett** · 02-19-2010, 09:33 AM

Thanks. I knew it would be a simple function, but I just couldn't find it. You guys make this look too easy.

**jmoffett** · 02-19-2010, 08:10 PM

How would I make this function copy as a relative reference. I anchor my reference to animal!A1 so my worksheet name doesn't move, but I want to copy the function to adjoining cells (animal!B5, animal!C5, animal!B6, animal!C6 etc.) so that it reads the contents of dog!B5, dog!C5, dog!B6, dog!C6, cat!B5, horse!B5 etc. depending the value of the worksheet name in animal!A1?

**DonkeyOte** · 02-20-2010, 04:55 AM

It would help if we knew where you were placing the formula and in which direction you were dragging.

Let's assume for sake of simplicity that the reference to B5 is in B5, C5 in C5 and so on and so forth such that the current sheet mirrors the sheet as referenced in A1.

B5: =INDIRECT(ADDRESS(ROW(),COLUMN(),,,$A$1))
copied across to right (and down as required)

If it is an exact mirror you could also use

B5: =INDIRECT("'"&$A$1&"'!"&CELL("address",B5))
copied across to right (and down as required)

You may find the Address construct easier to adapt.

**jmoffett** · 02-20-2010, 10:41 AM

That works. Sorry for the limited information, but I thought the function would copy and paste in a relative manner. Thanks for your help.

**jmoffett** · 02-20-2010, 04:41 PM

Will the INDIRECT function work within the data range requirement for VLOOKUP?

For instance, on the following,

=VLOOKUP(A1,horse!A1:G100,3)

if I wanted to replace the worksheet name "horse" with the content of a cell containing worksheet names, how would that look? For now, let's assume the cell range (A1:G100) would be the same for each worksheet.

**DonkeyOte** · 02-20-2010, 04:43 PM

You can use INDIRECT to create any range - embed as appropriate.

=VLOOKUP(A1,INDIRECT("'"&$B$1&"'!A1:G100"),3)

where B1 contains sheet name.

(but remember INDIRECT is volatile - be wary of over use - see link in sig. for more info. on Volatility)