Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Melbourne Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset funtion challenges for date selection

    Greetings and salutations. I have set up a spreadsheet which is used as an early-warning device for the executives of the business for which I work. I have copied and simplified one of the lines for your perusal - which is attached. The issue I have is that due to the layout of the sheet (which complies with the July - June financial year standards in Australia), my fancy formula works beautifully for the first six months of the financial year, by determining the range of cells to be included by taking the month and subtracting 6. Thus, the first month of the financial year, July, is month 7, but I want to include only 1 cell, so by subtracting 6 from the month it works. And August, September, October, November and December all work nicely. But January to June obviously don't, because (for example, January) 1-6 = -5. Can anyone suggest a workaround for this?

    Thank you in advance.

    Justin.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In D1:
    =MATCH(TEXT(ReportingMonth,"mmm"),$J$3:$U$3,0)
    then in E4:
    =(SUM(OFFSET(I5,0,1,1,$D$1))-SUM(OFFSET(I4,0,1,1,$D$1)))*17000
    and copy down as needed.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Dec 2010
    Location
    Melbourne Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A beatifully neat solution, thank you!

Posting Permissions

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