# Thread: Offset funtion challenges for date selection

1. ## 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?

Justin.

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

3. 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
•