3-D Relative Reference

I am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.

In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).

Unexpectedly, this formula:
=ISUM('201206:201305'!M6)
gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.

I can find no reference at all to converting the above formula from absolute to relative reference. Can anyone point me in the right direction?


Thanks,

yan

Hi FDibbins, and thanks for the welcome!

To answer your second question first, this worksheet started as a bill-paying tracker back in 2004 (in Quattro). The actual contents of each sheet can change from month to month. Certain cells, however, have fixed locations, allowing me to keep tabs of certain numbers.

Alas, INDIRECT is also incapable of relative 3-D addresses. Indeed, Excel does not seem to allow me to drill down through an arbitrary twelve sheets. I can find nothing on the 'net, and everything I've tried resolves to absolute references, or fails.

In the meantime I have come up with one of the ugliest kludges ever. I include it here as I've seen numerous requests for relative 3-D addresses but no answers. This one should work where the number of sheets is fixed and small. I would really like to replace this with proper relative 3-D addresses, though.

It starts at row 31, column M, and cell $A$2 holds the date value of the first day of the month:

HTML Code: 

Underneath Column M goes this formula, and this gives the desired relatively referenced sum:
=SUM(INDIRECT(M$32),INDIRECT(M$33),INDIRECT(M$34),INDIRECT(M$35),INDIRECT(M$36),INDIRECT(M$37),INDIRECT(M$38),INDIRECT(M$39),INDIRECT(M$40),INDIRECT(M$41),INDIRECT(M$42),INDIRECT(M$43))

Briefly, you calculate the names of the sheets in your range, starting with the current one (columns O, P, Q, and R). This must start with a label internal to the current sheet which identifies its order in the sheet stack; in the above example it is a date, stored in $A$2. From this you calculate the address of each of the target cells in the range, as text (column N); this converts the relative address range you need into an explicit list of absolute addresses, which Excel can handle. You can then use INDIRECT to reference these addresses (column M references one such address range).

At the start of September I will copy August's sheet, 201308, to the next sheet, fiddle with it a bit -- then expect all the formulas to work just like they did in August, including the ones that give 12 month to date calcuations.

This means that when I copy the formula ='201307'!M16 (last month's value in cell M16) from sheeet 201308 to 201309 it should change to ='201308'!M16 -- still giving last month's value in cell M16. It doesn't. It continues to give the value from sheet 201307. Since the address doesn't change when it's relative position changes, it's an absolute address.

I want the sheet references to behave like relative cell references -- everything should update to its new position, one sheet down the stack.


Yan