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
 

I'm not sure what you want, but let me make a stab at it.

Let's say you have the following:
A1=Sheet2
A2 = numeric (from your formula) - let's say it's 23

I'm now assuming you want to concatenate these two cells.


=A1&A2
=Sheet223
I'm guessing this isn't what you want. You probably want something more
like this


=A1&"!A"&A2
=Sheet2!A23

This will not give you the value in Sheet2!A23. What you need is the
following:

=INDIRECT(A1&"!A"&A2)

If this isn't what you want, please come back and clarify.

"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
 

=INDIRECT("'" & A1 & "'!B1")

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Variable Links to Tabsheets
 

Alberto Pinto wrote:
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

Sorry I couldn't answer sooner!

Thank you for your help!