Use varaible for worsheet name in a formule
 

Hi,

In a workbook, I have several worksheet with some figures related to a
specific year (one worksheet by year).
I have a worksheet to perform some calculation and so I use formulas like
this:
=YE04!B5-YE01!B5 where YE04 and YE01 are worsheet names.

I want to use variable to reference worsheet name. Variables will be two
cells in a separate worksheet where I put worsheet names to use.
1rst variable, called "start",will be cell A3 in worsheet "Reference" and
2nd varaible, called "end", will be cell B3 in worsheet "Reference".
I want to be able to write formula like =start!B5-end!B5.

Is it possible? if yes how to do?

Thanks

Use varaible for worsheet name in a formule
 

=INDIRECT(Reference!A3&"!B5")-INDIRECT(Reference!B3&"!B5)

--
HTH

Bob Phillips

"slm" wrote in message
...
Hi,

In a workbook, I have several worksheet with some figures related to a
specific year (one worksheet by year).
I have a worksheet to perform some calculation and so I use formulas like
this:
=YE04!B5-YE01!B5 where YE04 and YE01 are worsheet names.

I want to use variable to reference worsheet name. Variables will be two
cells in a separate worksheet where I put worsheet names to use.
1rst variable, called "start",will be cell A3 in worsheet "Reference" and
2nd varaible, called "end", will be cell B3 in worsheet "Reference".
I want to be able to write formula like =start!B5-end!B5.

Is it possible? if yes how to do?

Thanks

Use varaible for worsheet name in a formule
 

For example (do not use the apostrophes in the following) :-

1. Insert/Name/Define 2 cells with names 'start' and 'end'
2. In the 'start' cell I put 'Sheet1'
3. In the 'end' cell I put 'Sheet2'

I can now use the formula :-
=INDIRECT(CONCATENATE(end,"!","A1"))-INDIRECT(CONCATENATE(start,"!","A1"))

In the formula the range A1 is an explicit string with quotes.
It would be possible to use cell contents as for the sheet names
instead.


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=388283

Use varaible for worsheet name in a formule
 

It works well.

Thanks you very much


"Bob Phillips" wrote:

=INDIRECT(Reference!A3&"!B5")-INDIRECT(Reference!B3&"!B5)

--
HTH

Bob Phillips

"slm" wrote in message
...
Hi,

In a workbook, I have several worksheet with some figures related to a
specific year (one worksheet by year).
I have a worksheet to perform some calculation and so I use formulas like
this:
=YE04!B5-YE01!B5 where YE04 and YE01 are worsheet names.

I want to use variable to reference worsheet name. Variables will be two
cells in a separate worksheet where I put worsheet names to use.
1rst variable, called "start",will be cell A3 in worsheet "Reference" and
2nd varaible, called "end", will be cell B3 in worsheet "Reference".
I want to be able to write formula like =start!B5-end!B5.

Is it possible? if yes how to do?

Thanks