Indirect Addressing of Worksheets

[sandbag]

Probably an easy question, but I can't find the answer in the Excel Help
files.

I want to be able to construct a worksheet reference from the contents
of a cell.

For example, I have 2 worksheets called "Week1" & "Week2". I have a 3rd
worksheet called "Data".

If "Data!A1" is equal to "1", I want "Data!B1" to equal "Week1!B1".

If "Data!A1" is equal to "2", I want "Data!B1" to equal "Week2!B1".

I would have thought that Excel contained some indirect addressing
capabilities, but I can't find it.

 

[AlfD]

Hi!

If you have described the full extent (or thereabouts) of the problem
try:

In cell Data!A1 put =IF(Data!B1=1,Week1!B1,IF(Data!B1=2,Week2!B1,""))

Al

 

[Domenic]

Hi,

Try putting this formula in B1 of your Data sheet:

=INDIRECT("Week"&A1&"!B1")

Hope this helps!

 

[sandbag]

Thanks AlfD, however I will have 52 worksheets "Week1" to "Week52".
just used 2 sheets in my example for brevity

 

[JE McGimpsey]

one way:

=INDIRECT("'Week" & data!A1 & "'!B1")

The apostrophes (') are really only required if the sheet name may have
a space in it.

 

[AlfD]

Hi!
Quite! I thought there would be n other sheets waiting to ambush me.

So: the following does the sort of job you want. May need a bit o
refining. It assumes you are continuing your (useful) sheet numberin
system.

Put ="Week"&B1&"!"&"B1" in A1.
Put the number of the sheet you want to invoke in B1
Put INDIRECT(A1) in C1

C1 will show you the contents of B1 on the chosen sheet.

Al

 

[sandbag]

Thanks to all who responded.

INDIRECT is the answer to my problem. I don't know why I couldn't fin
it in the help files; I must have been more tired than I thought. :