variable in a link where the variable is the name of the sheet

[darrelly]

Hi all-

I'm new here and hope that someone will be able to help me with this.
am having an excel link problem.

Here is the scenario in basic terms. I have one workbook, in th
workbook i have 4 tabs labeled MASTER, SCENARIO 1, SCENARIO 2 an
SCENARIO 3

Scenario's all have the same format but have many different numbers i
them. The Master sheet has cells linked to scenario 1. A simple link o
the Master sheet would show up as follows:

='scenario 1'!A1

which basically means that it is pulling from cell A1 on the scenari
tab. Is it possible to have the "1" in this formula linked to anothe
cell so that if i just typed in 1 in that cell it would pull fro
scenario 1's spreadsheet but if type in 2 it will automatically pul
from scenario 2's spreadsheet? I can't figure out how to put a lin
within a link or if it is even possible. I hope my question make
sense. Thanks for the help!

Darrel

 

[Max]

Below's one response to your identical post in microsoft.public.excel
(Please do not multi-post):

One way to set it up is to use INDIRECT

Tinker around with this to get a prelim hang of using it ..

In MASTER
------------
Put in A1: SCENARIO (the common "alpha" part of the sheetname)
Put in B1: A1 (the target cell you want to link to in
each sheet)

Put in A2:A4 : 1,2,3
(these will be the variable "numeric" parts of the sheetnames)

Now put in B2: =INDIRECT("'"& $A$1 &" "& A2 & "'!"& B$1)
Copy B2 down to B4

B2:B4 will return the same as having link formulas in B2:B4 :
='SCENARIO 1'!A1
='SCENARIO 2'!A1
='SCENARIO 3'!A1

The concat text strings evaluated within the parens will be resolved by
INDIRECT to yield the desired results.