Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have a worksheet which I have attached with a formula to add up the previous month data based on the number of Months in cell N1. The year runs from Oct to Sept. period 8 Represents the Period Oct to May of the previous year

    I have use the formula =SUM(AH2:INDEX(2:2,34+$N$1-1)) to calculate the data from Oct 2007 to May 2008 and it is correctly calculated

    The problem that i have is that each month, I new column will be inserted where column W is and the current months data will be copied into column W.

    The formula must stay as =SUM(AH2:INDEX(2:2,34+$N$1-1)

    The formula however changes to =SUM(AI2:INDEX(2:2,34+$N$1-1)) , where a new column is inserted

    It would be appreciated if you would assist

    Regards

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use

    =SUM(INDIRECT("AH2"):INDEX(2:2,34+$N$1-1))

    or

    =SUM(OFFSET(INDIRECT("AH2"),0,0,1,$N$1))

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [quote name='HansV' post='783805' date='09-Jul-2009 17:45']You could use

    =SUM(INDIRECT("AH2"):INDEX(2:2,34+$N$1-1))

    or

    =SUM(OFFSET(INDIRECT("AH2"),0,0,1,$N$1))[/quote]

    Hi Hans

    Thanks for the help. This is much appreciated

    Regards

    Howard

Posting Permissions

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