Worksheet Names

[chalky]

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

EG. If each sheet refers to a case, then the master has a list of 10 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 to 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 (lets say C5) so i just need to to change the sheet reference. I have tried using CONCATENATE preceeded by the INDIRECT formula but this is 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" <chalky.1y6fho_1131450921.7613@excelforum-nospam.com> wrote in
message news:chalky.1y6fho_1131450921.7613@excelforum-nospam.com...
>
> I have a workbook with multiple worksheets. If i have a master sheet
> with a list of sheet references is there a way i can reference a cell
> via the sheet reference?
>
> EG. If each sheet refers to a case, then the master has a list of 10
> 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 to
> 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 (lets
> say C5) so i just need to to change the sheet reference. I have tried
> using CONCATENATE preceeded by the INDIRECT formula but this is
> returning #REF! Any ideas?
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=483089
>

[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)


"chalky" <chalky.1y6hkm_1131453603.1616@excelforum-nospam.com> wrote in
message news:chalky.1y6hkm_1131453603.1616@excelforum-nospam.com...
>
> 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
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=483089
>