If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Indirect Addressing of Worksheets
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. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Indirect Addressing of Worksheets
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,"")) Alf --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Indirect Addressing of Worksheets
Hi,
Try putting this formula in B1 of your Data sheet: =INDIRECT("Week"&A1&"!B1") Hope this helps! In article , sandbag wrote: 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. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Indirect Addressing of Worksheets
Thanks AlfD, however I will have 52 worksheets "Week1" to "Week52". I
just used 2 sheets in my example for brevity. --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
Indirect Addressing of Worksheets
one way:
=INDIRECT("'Week" & data!A1 & "'!B1") The apostrophes (') are really only required if the sheet name may have a space in it. In article , sandbag wrote: 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. --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
Indirect Addressing of Worksheets
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 of refining. It assumes you are continuing your (useful) sheet numbering 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. Alf --- Message posted from http://www.ExcelForum.com/ |
#7
|
|||
|
|||
Indirect Addressing of Worksheets
Thanks to all who responded.
INDIRECT is the answer to my problem. I don't know why I couldn't find it in the help files; I must have been more tired than I thought. --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|