Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can I compute the bounds of a 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: =Sum(Jan:Dec!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) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "AFD at FONO" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing if a point is falling within the bounds of intersecting cu | Excel Worksheet Functions | |||
Testing if a point is falling within the bounds of intersecting cu | Excel Discussion (Misc queries) | |||
test for falling with the bounds of intersecting 2 dimensional cur | Excel Worksheet Functions | |||
How to fix lower and upper bounds for data | Excel Worksheet Functions | |||
How to compute overtime pay | New Users to Excel |