Variable Links to tabsheets

[Alberto Pinto]

Hi!

How can I make a link to another sheet be dependable on one cell value
(without ifs)?

I give you an example:

I have a worksheet with three sheets ('Sheet1','Sheet2', and 'Sheet3')
In 'Sheet1' I have two cells:
A1 - Its value can be 'Sheet2' or 'Sheet3'
A2 - It has a formula (for example: 'Sheet2'!B1)

I would like to make a formula like this one for cell A2: *A1*!B1
and the Excel to "substitute" the * for Sheet2 or Sheet3 depending on
the value in A1.

I hope i explained it ok.

Thanks in advance

 

[rsenn]

How about using a simple if statement


If (A1 ="Sheet2",'Sheet2'!A1*'Sheet2'B1,'Sheet3'!A1*'Sheet3'!B1

 

[Max]

Think you're looking for INDIRECT ..

Put in A1: Sheet2
Put in A2: =INDIRECT("'"& A1 &"'!B1")

A2 returns the same as : =Sheet2!B1

Changing the sheetname in A1 to: Sheet3
will then return the value in Sheet3's B1

 

[noyb]

Please, what does the ' as in ("'" do in this formula
Thanks

 

[Max]

.. what does the ' as in ("'" do in this formula ..

The single quotes / apostrophes (there's actually a pair of them) allows
any worksheet name to be accommodated in A1

Best that I quote this advisory from a post by Harlan:

".. *ALWAYS* (and I *DO* mean **ALWAYS**) include single quotes around
worksheet names when constructing textrefs inside INDIRECT calls. The result
is far more robust since without them worksheet names that contain spaces or
exclamation points (along with a few others that require single quote
delimiters) will choke INDIRECT so that it returns #REF!. Even if the
worksheet name doesn't require single quote delimiters, no harm done adding
them. .. "

 

[noyb]

Thanks

The single quotes / apostrophes (there's actually a pair of them) allows
any worksheet name to be accommodated in A1

Best that I quote this advisory from a post by Harlan:

".. *ALWAYS* (and I *DO* mean **ALWAYS**) include single quotes around
worksheet names when constructing textrefs inside INDIRECT calls. The result
is far more robust since without them worksheet names that contain spaces or
exclamation points (along with a few others that require single quote
delimiters) will choke INDIRECT so that it returns #REF!. Even if the
worksheet name doesn't require single quote delimiters, no harm done adding
them. .. "

 

[Max]

Delighted !