can I compute the bounds of a 3D reference

[AFD at FONO]

An example. I have a workbook in which the worksheets are named for each
month of the year: Jan through Dec (3 letter names) On each worksheet the
same cell contains the expenses for the month. I also have a cell that
contains the year to date (YTD) expenses. I'd like to be able to compute
YTD as SUM(Jan:current_month!cell). Cell refers to the acutal monthly
expense on each worksheet. I can get current_month, the name of the
worksheet, using the CELL function. However, Excel doesn't seem to like any
kind of expression in the 3D reference.

 

[RagDyer]

You shouldn't need any complicated 3D reference!

Just make sure that the cell containing the monthly expenses, the one that's
in the same location on every sheet, doesn't return any values until you're
in that particular month.
In other words, keep it zero until that month arrives.

Now, saying that the monthly total cell is A50, a simple formula like:

=Sum(Janec!A50)

will give you the YTD total.

The rule for this type of formula is that *any sheet*, physically sandwiched
between Jan and Dec will be totaled.
As long as the A50 cells are zero or empty on the future month's sheets,
you'll see your YTD total.

You can even drag various tabs out of the sandwich to get "what-if" totals,
as well as add extra sheets to see different scenarios.

Many people add 2 sheets to the beginning and end of the WB and name them
"Start" and "End", and then hide them.

Then use a formula like:
=Sum(Start:End!A1)
Or even:
=Sum(Start:End!A1:A10)