can I compute the bounds of a 3D reference



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.


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:


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:
Or even:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question