Use varaible for worsheet name in a formule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Back
Top