Assuming that you have a date from the current month on the sheet in, say, cell A2, you could use INDIRECT to determine the previous and next month's sheet names.
|
A |
B |
C |
D |
1 |
This Month |
Last Month |
Next Month |
|
2 |
February |
Jan 01 - Jan 31 |
Mar 01 - Mar 31 |
|
3 |
February |
January |
March |
|
4 |
=J25 |
|
|
|
5 |
|
=INDIRECT("'" & B$2 & "'!J25") |
|
|
6 |
|
|
=INDIRECT("'" & C$2 & "'!J25") |
|
7 |
|
|
|
|
8 |
B2: |
Last Month |
|
|
9 |
=TEXT(DATE(YEAR($A2),MONTH($A2)-1,1),"mmm dd") & " - " & TEXT(DATE(YEAR($A2),MONTH($A2),0),"mmm dd") |
|
|
|
10 |
|
|
|
|
11 |
C2: |
Next Month |
|
|
12 |
=TEXT(DATE(YEAR($A2),MONTH($A2)+1,1),"mmm dd") & " - " & TEXT(DATE(YEAR($A2),MONTH($A2)+2,0),"mmm dd") |
|
|
|
13 |
|
|
|
|
Cell A2 contains 01/02/2014, 1 February, 2014.
B2:
Formula:
=TEXT(DATE(YEAR($A2),MONTH($A2)-1,1),"mmm dd") & " - " & TEXT(DATE(YEAR($A2),MONTH($A2),0),"mmm dd")
C2:
Formula:
=TEXT(DATE(YEAR($A2),MONTH($A2)+1,1),"mmm dd") & " - " & TEXT(DATE(YEAR($A2),MONTH($A2)+2,0),"mmm dd")
B3:
Formula:
=INDIRECT("'" & B$2 & "'!J25")
C3:
Formula:
=INDIRECT("'" & C$2 & "'!J25")
See the attached example workbook.
Regards, TMS
Bookmarks