Referencing worksheet name in formula

[Alan D.]

I am trying to find an elegant way to refer a worksheet name in an easily
copied formula. In this particular example, I have a sequence of
month-specific worksheets, eleven of which reference the month prior to
bring up YTD figures. I've just become acquainted with the INDIRECT function
but that seems a bit cumbersome, as it entails either modifying the cell
reference portion of the formula (e.g., =INDIRECT($A$37&"A16")+D16, where
$A$37 references the previous month) or requires a reference cell on each
sheet for each desired referenced field. I'd like to find a reference that I
can easily drag and copy down a column and duplicate to each month's sheet
without all the supporting reference cells (e.g., =PreviousMonthReference!
D16+D16), where the reference is either a simple equation (e.g.,
CurrentSheet -1 if the months are assigned numeric names) or a single lookup
table (e.g., =LOOKUP(CurrentSheetName,Table,2)). Thanks for any
suggestions.

 

[Frank Kabel]

Hi
one way:
If you have named your worksheets with a month name you can use the
following formulas:
1. To get the current worksheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
place this in lets say cell A1

2. To get the previous month name:
=TEXT(DATEVALUE("1-" & A1 & "-2004")-1,"MMMM")
-> you have to adapt the DATEVALUE parameter to your regional settings
(this works for German date settings: "MMMM-DD-YYYY").
Lets say you placed this formula in B1

3. Now you can use INDIRECT with the result from B1:
=INDIRECT("'" & B1 & "'!A16")+D16
note the multiple apostrophes (" ' " at the beginning and " ' ! in the
middle)

Of course you can combine this to one formula:
=INDIRECT("'" & TEXT(DATEVALUE("1-" &
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) &
"-2004")-1,"MMMM") & "'!A16")+D16

 

[Jacques Brun]

Alan
consider the following formul

=INDIRECT("January"&"!"&CELL("address",A9))+A

it adds in the current cell the contents of cell A9 of the current sheet with the contents of cell A9 in another sheet called January. This formula can be copied to another cell without editing it. You can also store the value "January" in one cell (e.g. A1) and change the formula into

=INDIRECT($A$1&"!"&CELL("address",A9))+A

Then you can duplicate a sheet to create the next month and all you have to do is to change the name of the previous month sheet in cell A1 of the new shee

If you want to add the column C of the same row in the previous month with column A of this month, the formula should oubviously b

=INDIRECT("A1"&"!"&CELL("address",C9))+A

Hopping it can help
Jacque
----- Alan D. wrote: ----

I am trying to find an elegant way to refer a worksheet name in an easil
copied formula. In this particular example, I have a sequence o
month-specific worksheets, eleven of which reference the month prior t
bring up YTD figures. I've just become acquainted with the INDIRECT functio
but that seems a bit cumbersome, as it entails either modifying the cel
reference portion of the formula (e.g., =INDIRECT($A$37&"A16")+D16, wher
$A$37 references the previous month) or requires a reference cell on eac
sheet for each desired referenced field. I'd like to find a reference that
can easily drag and copy down a column and duplicate to each month's shee
without all the supporting reference cells (e.g., =PreviousMonthReference
D16+D16), where the reference is either a simple equation (e.g.
CurrentSheet -1 if the months are assigned numeric names) or a single looku
table (e.g., =LOOKUP(CurrentSheetName,Table,2)). Thanks for an
suggestions