Worksheet Names

[chalky]

I have a workbook with multiple worksheets. If i have a master shee
with a list of sheet references is there a way i can reference a cel
via the sheet reference?

EG. If each sheet refers to a case, then the master has a list of 1
cases in column A (which will change).

Sheet Ref Case Value
1 A
10 B
15 F
5 C
2 D
3 I

(Please note the sheet ref is the number and the letter is supposed t
be a case name but the formating is moving it too close together...)

The value i am looking for will be in the same cell in each sheet (let
say C5) so i just need to to change the sheet reference. I have trie
using CONCATENATE preceeded by the INDIRECT formula but this i
returning #REF! Any ideas

 

[Bob Phillips]

If your sheet name is say 1A|, then use

=INDIRECT("'"&A1&B1&"'!C5")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[chalky]

Ah perfect, that is exactly what i was trying to get to with CONCATENATE
but your way is loads better. Well for a start it works!

Cheers
Chris

 

[Bob Phillips]

Chris,

CONCATENATE is a completely unnecessary function, & does it just as well,
and is more self-explanatory IMO.

--

HTH

RP
(remove nothere from the email address if mailing direct)