evaluate cell value with sheet name
 

I have different sheet names in column A. I would like to use the
sheet value in A to show a value in a specific cell in that sheet.

For example:

A12 has 'Test 1 & 2' (no 's) and I would like cells b12 and c12 to be
'Test 1 & 2'!A50 and 'Test 1 & 2'!A60 respectively. However, I do not
want to hard code the 'Test 1 & 2' text but would rather use the fact
that this string is already available in cell A12.

How can I do this?

Thanks

evaluate cell value with sheet name
 

=INDIRECT($A12&"!"&"A50") in B12

=INDIRECT($A12&"!"&"A60") in C12

Drag/copy down or up if you have more sheet names in column A

Note that A50 and A60 are text and will not increment.


Gord Dibben MS Excel MVP

On Thu, 04 Oct 2007 03:42:03 -0000, wrote:

I have different sheet names in column A. I would like to use the
sheet value in A to show a value in a specific cell in that sheet.

For example:

A12 has 'Test 1 & 2' (no 's) and I would like cells b12 and c12 to be
'Test 1 & 2'!A50 and 'Test 1 & 2'!A60 respectively. However, I do not
want to hard code the 'Test 1 & 2' text but would rather use the fact
that this string is already available in cell A12.

How can I do this?

Thanks

evaluate cell value with sheet name
 

On Oct 4, 12:17 am, Gord Dibben <gorddibbATshawDOTca wrote:
=INDIRECT($A12&"!"&"A50") in B12

=INDIRECT($A12&"!"&"A60") in C12

Drag/copy down or up if you have more sheet names in column A

Note that A50 and A60 are text and will not increment.

Gord Dibben MS Excel MVP

On Thu, 04 Oct 2007 03:42:03 -0000, wrote:
I have different sheet names in column A. I would like to use the
sheet value in A to show a value in a specific cell in that sheet.

For example:

A12 has 'Test 1 & 2' (no 's) and I would like cells b12 and c12 to be
'Test 1 & 2'!A50 and 'Test 1 & 2'!A60 respectively. However, I do not
want to hard code the 'Test 1 & 2' text but would rather use the fact
that this string is already available in cell A12.

How can I do this?

Thanks

Thanks. That does work if the sheet names is a single word. However,
my sheets have several spaces and some symbols such as ',' and '&'.
How can I use them with this method?

Thanks again.

evaluate cell value with sheet name
 

If you have spaces in the sheet name you must enclose them with
apostrophes, so amend Gord's formula like this:

=INDIRECT("'"&$A12&"'!"&"A50") in B12

=INDIRECT("'"&$A12&"'!"&"A60") in C12

It's a bit difficult to see, so that is <quotes<apostrophe<quotes
after the bracket and another apostrophe between the <quotes and !

Hope this helps.

Pete


On Oct 4, 11:24 am, wrote:

Thanks. That does work if the sheet names is a single word. However,
my sheets have several spaces and some symbols such as ',' and '&'.
How can I use them with this method?

Thanks again.- Hide quoted text -

- Show quoted text -

evaluate cell value with sheet name
 

Your example showed you already had the single quotes around the sheet name so I
did not include them in my formula.

'Test 1 & 2'

If you don't have the single quotes you must amend as Pete_UK has pointed out.


Gord Dibben MS Excel MVP

On Thu, 04 Oct 2007 10:24:10 -0000, wrote:

On Oct 4, 12:17 am, Gord Dibben <gorddibbATshawDOTca wrote:
=INDIRECT($A12&"!"&"A50") in B12

=INDIRECT($A12&"!"&"A60") in C12

Drag/copy down or up if you have more sheet names in column A

Note that A50 and A60 are text and will not increment.

Gord Dibben MS Excel MVP

On Thu, 04 Oct 2007 03:42:03 -0000, wrote:
I have different sheet names in column A. I would like to use the
sheet value in A to show a value in a specific cell in that sheet.

For example:

A12 has 'Test 1 & 2' (no 's) and I would like cells b12 and c12 to be
'Test 1 & 2'!A50 and 'Test 1 & 2'!A60 respectively. However, I do not
want to hard code the 'Test 1 & 2' text but would rather use the fact
that this string is already available in cell A12.

How can I do this?

Thanks

Thanks. That does work if the sheet names is a single word. However,
my sheets have several spaces and some symbols such as ',' and '&'.
How can I use them with this method?

Thanks again.

evaluate cell value with sheet name
 

Thanks to both of you!