+ Reply to Thread
Results 1 to 6 of 6

3-D Relative Reference

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    myob, ak
    MS-Off Ver
    Excel 2007
    Posts
    4

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    Re: 3-D Relative Reference

    Hi and welcome to the forum

    That is 1 of the annoying "improvements" in 2007. when you copy a formula from 1 sheet to the next, it doesnt change the sheet ref

    you might consider using INDIRECT() instead, to reference the sheet names?

    However, I would question the need to have a sheet for each month? If you put ALL data into 1 sheet, you could then use other sheets for extraction/analysis etc
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    myob, ak
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: 3-D Relative Reference

    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: 
    M           N               O                 P                     Q                    R
    M6          Sheet Ref       Name (Text)       Year (Num)	    Month (Num)          Month (Text)
    =$N32M$31   ="'"&O32&"'!"   =TEXT(P32,0)&R32  =YEAR($A$2)           =MONTH($A$2)         =RIGHT("0"&TEXT(Q32,0),2)
    =N33&M$31   ="'"&O33&"'!"   =TEXT(P33,0)&R33  =IF(Q32=1,P32-1,P32)  =IF(Q32=1,12,Q32-1)  =RIGHT("0"&TEXT(Q33,0),2)
    =N34&M$31   ="'"&O34&"'!"   =TEXT(P34,0)&R34  =IF(Q33=1,P33-1,P33)  =IF(Q33=1,12,Q33-1)  =RIGHT("0"&TEXT(Q34,0),2)
    =N35&M$31   ="'"&O35&"'!"   =TEXT(P35,0)&R35  =IF(Q34=1,P34-1,P34)  =IF(Q34=1,12,Q34-1)	 =RIGHT("0"&TEXT(Q35,0),2)
    =N36&M$31   ="'"&O36&"'!"   =TEXT(P36,0)&R36  =IF(Q35=1,P35-1,P35)  =IF(Q35=1,12,Q35-1)  =RIGHT("0"&TEXT(Q36,0),2)
    =N37&M$31   ="'"&O37&"'!"   =TEXT(P37,0)&R37  =IF(Q36=1,P36-1,P36)  =IF(Q36=1,12,Q36-1)  =RIGHT("0"&TEXT(Q37,0),2)
    =N38&M$31   ="'"&O38&"'!"   =TEXT(P38,0)&R38  =IF(Q37=1,P37-1,P37)  =IF(Q37=1,12,Q37-1)  =RIGHT("0"&TEXT(Q38,0),2)
    =N39&M$31   ="'"&O39&"'!"   =TEXT(P39,0)&R39  =IF(Q38=1,P38-1,P38)  =IF(Q38=1,12,Q38-1)  =RIGHT("0"&TEXT(Q39,0),2)
    =N40&M$31   ="'"&O40&"'!"   =TEXT(P40,0)&R40  =IF(Q39=1,P39-1,P39)  =IF(Q39=1,12,Q39-1)  =RIGHT("0"&TEXT(Q40,0),2)
    =N41&M$31   ="'"&O41&"'!"   =TEXT(P41,0)&R41  =IF(Q40=1,P40-1,P40)  =IF(Q40=1,12,Q40-1)  =RIGHT("0"&TEXT(Q41,0),2)
    =N42&M$31   ="'"&O42&"'!"   =TEXT(P42,0)&R42  =IF(Q41=1,P41-1,P41)  =IF(Q41=1,12,Q41-1)  =RIGHT("0"&TEXT(Q42,0),2)
    =N43&M$31   ="'"&O43&"'!"   =TEXT(P43,0)&R43  =IF(Q42=1,P42-1,P42)  =IF(Q42=1,12,Q42-1)  =RIGHT("0"&TEXT(Q43,0),2)

    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).

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    Re: 3-D Relative Reference

    Alas, INDIRECT is also incapable of relative 3-D addresses
    what exactly do you mean by this?

    relative sheet name!...relative column...relative row? If thats the case, then yes it can

  5. #5
    Registered User
    Join Date
    08-12-2013
    Location
    myob, ak
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: 3-D Relative Reference

    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

  6. #6
    Registered User
    Join Date
    08-12-2013
    Location
    myob, ak
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: 3-D Relative Reference

    As to INDIRECT, I need to replace the cell range in
    SUM('201207:201306'!M6)
    and I can find no reference anywhere that this is possible.

    For instance, if I put
    '201207:201306'!M6 in cell R23
    and
    =SUM(INDIRECT(R23)) in another cell
    it fails to resolve the reference.
    Last edited by yandoodan; 08-13-2013 at 05:03 PM. Reason: Clarify via formatting

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Relative Reference
    By jassbru in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2012, 11:39 PM
  2. Replies: 5
    Last Post: 09-21-2012, 02:08 PM
  3. change from relative reference to absolute reference
    By ronlau123 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-13-2011, 04:57 AM
  4. Relative Reference
    By JDMCMAMC815 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2010, 02:33 PM
  5. Replies: 0
    Last Post: 11-15-2007, 02:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1