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

=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

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.

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 -

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.

Thanks to both of you!