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

Hi all-

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

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

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

='scenario 1'!A1

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

Darrell


--
darrelly
------------------------------------------------------------------------
darrelly's Profile: http://www.excelforum.com/member.php...o&userid=27899
View this thread: http://www.excelforum.com/showthread...hreadid=474038

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--