Formula that uses Previous Sheet name

[amygibson]

I have created an excel workbook (2007) with multiple pages for payroll hours submission. Each sheet accrues flex time and I need this accrual to carry forward to the next pay period. I would like a formula that will use the name of the previous tab in the current sheet. Current formula is:

='May 15 - May 30'!J25

Unfortunately, I have to manually update each formula with the sheet name. Is it possible to build a formula that will do this for me?

~Amy

[Tony Valko]

See if you can adapt the technique used here:

https://www.excelforum.com/showthread.php?t=1000626

[shg]

You could use a UDF:

Please Login or Register  to view this content.

For your example,

=OtherSheet("J25")

... returns the value of J25 on the prior sheet.

[TMS]

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:

Please Login or Register  to view this content.

C2:

Please Login or Register  to view this content.

B3:

Please Login or Register  to view this content.

C3:

Please Login or Register  to view this content.

See the attached example workbook.


Regards, TMS