Variable Links to tabsheets
 

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

Variable Links to tabsheets
 

How about using a simple if statement


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


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=487830

Variable Links to tabsheets
 

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

Variable Links to tabsheets
 

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

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

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

Variable Links to tabsheets
 

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

Variable Links to tabsheets
 

Thanks

Max wrote:
"noyb" wrote

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

Variable Links to tabsheets
 

Delighted !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"noyb" wrote in message
...
Thanks