Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink varaible substitution - How To | Excel Worksheet Functions | |||
Disappearance of assigned value of varaible when working with form | Excel Programming | |||
need help with a formule | Excel Programming | |||
formule | Excel Programming | |||
Passing a named varaible to Excel's Autofilter | Excel Programming |