having a link within a link

[darrelly]

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

 

[Max]

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.

 

[darrelly]

thanks for the help

I tried everything you said but i get #Ref! in the b2 cell where i
have this forumla

=INDIRECT("'"& $A$1 &" "& A2 & "'!"& B$1)

i cut and pasted your formula so i think i got it right

 

[Max]

You might have had a stray white space or two somewhere inadvertently
especially in the text inputs made in A1 and/or B1, which is throwing the
matching of the concat strings off. Note that the 3 scenario sheets must of
course exist and the sheetnames must match with what's input into A1
(typos?)

Try again with this slightly more robust formula in B2:

=INDIRECT("'"& TRIM($A$1) &" "& TRIM($A2) & "'!"& TRIM(B$1))

Copy B2 down to B4 as before

[The formula can also be filled across to C2:C4,
if you have another target cell input into C1: B1 (say)]

Here's a sample file here to play around with:
http://cjoint.com/?khlCkjJVEX
darrelly_gen.xls

 

[darrelly]

I'll try that thanks. If I don't want the forumla to link to the b
cell but instead would like to put the actual cell i want linked ther
on each tab how do i change that formula. i guess what i'm trying t
say is that i don't want to make b1 a variable. thanks again for al
the help and your time.

Darrel

 

[darrelly]

PS - that sample sheet you sent really helps! Thanks! I just got t
figure out how to remove the b1 and c1 cells as variables and mak
them a set amount

 

[Max]

.. remove the b1 and c1 cells as variables and make them a set amount.

For the same set-up, maybe just put instead in B2 as:
=INDIRECT("'"& TRIM($A$1) &" "& TRIM($A2) & "'!A1")
and copy down

 

[darrelly]

thank you max--you are a savior!

best regards

Darrell

 

[darrelly]

Hi Max-

I've run into another problem regarding this. Here is my actual cel
forumla:

=E7*((Breakeven!$E$26+Breakeven!$E$27+Breakeven!$E$28+Breakeven!$E$32
+Breakeven!$E$33+Breakeven!$E$35+Breakeven!$E$37+Breakeven!$E$38+
Breakeven!$E$40+Breakeven!$E$42+Breakeven!$E$30+
Breakeven!$F$53*(Breakeven!$F$26+Breakeven!$F$28+
Breakeven!$F$32+Breakeven!$F$33+Breakeven!$F$30+
Breakeven!$F$35+Breakeven!$F$38+Breakeven!$F$40+
Breakeven!$F$42)+Breakeven!$H$53*(Breakeven!$H$26+
Breakeven!$H$32+Breakeven!$H$30+Breakeven!$H$38+
Breakeven!$H$40+Breakeven!$H$42)+Breakeven!$J$53*
(Breakeven!$J$26+Breakeven!$J$38+Breakeven!$J$42))*-1)

Basically it's a basic link formula with a lot of links. "Breakeven
is the sheet name that I would like to make a variable. When I use th
indirect formula for each individual link (i.e. Breakeven!$E$26) I got
super long formula simply because I have an indirect formula for eac
one. Is there a way to just use the indirect function for the entir
formula without having to do it for each one..otherwise excel gives m
an error that the forumla is too long....here is the forumla once I us
the indirect format:

=E7*((INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$E$26”
+INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$E$27”) +INDIRECT(“‘“
TRIM($J$6) &” “&TRIM($J7) & “‘!$E$28”) +INDIRECT(“‘“& TRIM($J$6) &
“&TRIM($J7) & “‘!$E$32”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$E$33”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$E$35”
+INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$E$37”) +INDIRECT(“‘“
TRIM($J$6) &” “&TRIM($J7) & “‘!$E$38”) +INDIRECT(“‘“& TRIM($J$6) &
“&TRIM($J7) & “‘!$E$40”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$E$42”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$E$30”
+INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$F$53”) *(INDIRECT(“‘“
TRIM($J$6) &” “&TRIM($J7) & “‘!$F$26”) +INDIRECT(“‘“& TRIM($J$6) &
“&TRIM($J7) & “‘!$F$28”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$F$32”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$F$33”
+INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$F$30”) +INDIRECT(“‘“
TRIM($J$6) &” “&TRIM($J7) & “‘!$F$35”) +INDIRECT(“‘“& TRIM($J$6) &
“&TRIM($J7) & “‘!$F$38”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$F$40”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$F$42”))+INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$H$53”
*(INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$H$26”) +INDIRECT(“‘“
TRIM($J$6) &” “&TRIM($J7) & “‘!$H$32”) +INDIRECT(“‘“& TRIM($J$6) &
“&TRIM($J7) & “‘!$H$30”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$H$38”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$H$40”
+INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$H$42”))+INDIRECT(“‘“
TRIM($J$6) &” “&TRIM($J7) & “‘!$J$53”) *(INDIRECT(“‘“& TRIM($J$6) &
“&TRIM($J7) & “‘!$J$26”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7)
“‘!$J$38”) +INDIRECT(“‘“& TRIM($J$6) &” “&TRIM($J7) & “‘!$J$42”)))*-1)


Wow! I hope you know an easier way :

 

[Max]

You're welcome !
Let's close this thread here (wrt your orig. query)

 

[Max]

Please put in as a new post.
You'd likely gather more/better insights from the many others

One way to reduce formula length is simply to use short sheetnames (and w/o
any spaces in the sheetname). Eg: if you'd replace "Breakeven" with say:
"BE",
there'd be an immediate/drastic cutback in the orig. formula's length

Wrt the suggested INDIRECT, we could remove the whitespaces within the
formula (they were put there to improve clarity) and perhaps remove the
TRIM(...) as well if these were actually found not necessary.

For example, the last suggested equation:
=INDIRECT("'"& TRIM($A$1) &" "& TRIM($A2) & "'!A1")

could be "trimmed" to just:
=INDIRECT("'"&$A$1&" "&$A2&"'!A1")

And there could be many other/better ways to reduce formula length,
or there could be better/shorter alternative formulas to achieve what you're
after.