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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"darrelly" <darrelly.1wieub_1128650704.631@excelforum-nospam.com> wrote in
message news:darrelly.1wieub_1128650704.631@excelforum-nospam.com...
>
> 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=474002
>

[darrelly]

thanks for the help! i think i got it!

in your example, you have me putting the cell i want in B1. Can I just put that cell into your indirect forumla instead of having it refer to b1? thanks again..much appreciated!

[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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"darrelly" <darrelly.1wivib_1128672304.0058@excelforum-nospam.com> wrote in
message news:darrelly.1wivib_1128672304.0058@excelforum-nospam.com...
>
> 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
>
>
> --
> darrelly
> ------------------------------------------------------------------------
> darrelly's Profile:
http://www.excelforum.com/member.php...o&userid=27899
> View this thread: http://www.excelforum.com/showthread...hreadid=474002
>

[darrelly]

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

Darrell

[darrelly]

PS - that sample sheet you sent really helps! Thanks! I just got to figure out how to remove the b1 and c1 cells as variables and make 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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 cell 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 the indirect formula for each individual link (i.e. Breakeven!$E$26) I got a super long formula simply because I have an indirect formula for each one. Is there a way to just use the indirect function for the entire formula without having to do it for each one..otherwise excel gives me an error that the forumla is too long....here is the forumla once I use 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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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