How can I reference "previous sheet"

[Marvin Hlavac]

Hi

Each workday I create a new sheet which I name e.g. 10-31-2003. I want to be
able to insert a formula like

=MAX('10-29-2003'!P1438)

but instead of the "10-29-2003" I want to insert something that Excel would
understand is "previous sheet". How can I do this?

Thanks for any help

 

[Peo Sjoblom]

One possible way

=MAX(INDIRECT("'"&TEXT(TODAY()-1,"mm-dd-yyyy")&"'!P1438"))

make sure you have a correct date on your computer since it use the system
clock.
I personally wouldn't use it. A little more static way would be

=MAX(INDIRECT("'"&TEXT(--MID(CELL("filename",A1),FIND("]",CELL("filename",A1
))+1,32)-1,"mm-dd-yyyy")&"'!P1438"))

 

[Marvin Hlavac]

=MAX(INDIRECT("'"&TEXT(TODAY()-1,"mm-dd-yyyy")&"'!P1438"))

make sure you have a correct date on your computer since it use the system
clock.
I personally wouldn't use it. A little more static way would be

=MAX(INDIRECT("'"&TEXT(--MID(CELL("filename",A1),FIND("]",CELL("filename",A1
))+1,32)-1,"mm-dd-yyyy")&"'!P1438"))

Hi Peo,

Thanks. I don't know much so all I can do is copy and paste the above
suggestions. The first one works if I work on today's worksheet and the
computer date is correct. If however I open a sheet from a few days ago
(sheet name e.g. 10-25-3003) or if it's Monday and there was no sheet
created on Sunday (day off) it will not work.

The second formula didn't work for some reason.

Peo, what I'm trying to do is this: Monday to Friday (but sometimes also
Saturday and Sunday) I open a new worksheet. They are always named by the
current date (e.g. 10-31-2003). I have invoice numbers in each daily sheet
in cells from P14 to P38. Instead of manually entering a new invoice number
on a new daily sheet I would like a new sheet to know what was the last
(highest) invoice number on the previous sheet. (Or on any sheet in the
workbook in cells P1438)

I spent several hours yesterday searching the Web how to reference "previous
sheet" in a workbook. So far no luck.

 

[jaf]

Hi Marvin,
This is modified to allow up to 5 days, in case you have a nice long
weekend. Vacations not included.

=MAX(INDIRECT("'"&TEXT(MAX(TODAY()-1,TODAY()-2,TODAY()-3,TODAY()-4,TODAY()-5
),"mm-dd-yyyy")&"'!a1:a10"))



--

John

johnf202 at hotmail dot com

=MAX(INDIRECT("'"&TEXT(TODAY()-1,"mm-dd-yyyy")&"'!P1438"))

make sure you have a correct date on your computer since it use the system
clock.
I personally wouldn't use it. A little more static way would be

=MAX(INDIRECT("'"&TEXT(--MID(CELL("filename",A1),FIND("]",CELL("filename",A1

))+1,32)-1,"mm-dd-yyyy")&"'!P1438"))

Hi Peo,

Thanks. I don't know much so all I can do is copy and paste the above
suggestions. The first one works if I work on today's worksheet and the
computer date is correct. If however I open a sheet from a few days ago
(sheet name e.g. 10-25-3003) or if it's Monday and there was no sheet
created on Sunday (day off) it will not work.

The second formula didn't work for some reason.

Peo, what I'm trying to do is this: Monday to Friday (but sometimes also
Saturday and Sunday) I open a new worksheet. They are always named by the
current date (e.g. 10-31-2003). I have invoice numbers in each daily sheet
in cells from P14 to P38. Instead of manually entering a new invoice number
on a new daily sheet I would like a new sheet to know what was the last
(highest) invoice number on the previous sheet. (Or on any sheet in the
workbook in cells P1438)

I spent several hours yesterday searching the Web how to reference "previous
sheet" in a workbook. So far no luck.

 

[Peo Sjoblom]

Martin,

Here's a VBA solution I once picked up from Chip Pearson,
that will always give you the previous sheet. Open your workbook,
press Alt + F11, select insert>module and paste the below into the
window

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

Press Alt + Q to close the editor and use this formula to return
the max value from the previous sheet

=MAX(INDIRECT("'"&PrevSheet()&"'!P1438"))




--

Regards,

Peo Sjoblom

=MAX(INDIRECT("'"&TEXT(TODAY()-1,"mm-dd-yyyy")&"'!P1438"))

make sure you have a correct date on your computer since it use the system
clock.
I personally wouldn't use it. A little more static way would be

=MAX(INDIRECT("'"&TEXT(--MID(CELL("filename",A1),FIND("]",CELL("filename",A1

))+1,32)-1,"mm-dd-yyyy")&"'!P1438"))

Hi Peo,

Thanks. I don't know much so all I can do is copy and paste the above
suggestions. The first one works if I work on today's worksheet and the
computer date is correct. If however I open a sheet from a few days ago
(sheet name e.g. 10-25-3003) or if it's Monday and there was no sheet
created on Sunday (day off) it will not work.

The second formula didn't work for some reason.

Peo, what I'm trying to do is this: Monday to Friday (but sometimes also
Saturday and Sunday) I open a new worksheet. They are always named by the
current date (e.g. 10-31-2003). I have invoice numbers in each daily sheet
in cells from P14 to P38. Instead of manually entering a new invoice number
on a new daily sheet I would like a new sheet to know what was the last
(highest) invoice number on the previous sheet. (Or on any sheet in the
workbook in cells P1438)

I spent several hours yesterday searching the Web how to reference "previous
sheet" in a workbook. So far no luck.

 

[Marvin Hlavac]

This is modified to allow up to 5 days, in case you have a nice long
weekend. Vacations not included.

=MAX(INDIRECT("'"&TEXT(MAX(TODAY()-1,TODAY()-2,TODAY()-3,TODAY()-4,TODAY()-5
),"mm-dd-yyyy")&"'!a1:a10"))

Hi John,

Thank you. The above suggested formula does solve the problem of a weekend
which is great but there is still one problem to solve. The formula only
works when worksheet named 10-31-2003 is opened and the current date is
10-31-2003. If I open the worksheet named 10-31-2003 the next day the
formula will not work.

The think is that I don't want to delete previous "daily sheets". I want to
keep them and I want to add a new one each working day.

Would a needed formula be easier to create if I didn't try to reference
"previous" sheet but rather "any" sheet in the workbook. Is it possible to
reference a range of cells (e.g. P1428) in "any" or rather "all"
worksheets of my workbook?

 

[Marvin Hlavac]

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

Press Alt + Q to close the editor and use this formula to return
the max value from the previous sheet

=MAX(INDIRECT("'"&PrevSheet()&"'!P1438"))

Peo that is exactly what I needed. It looks like it works!!! Thanks a
million to you Peo and also John.

 

[Peo Sjoblom]

My Pleasure Marvin

--

Regards,

Peo Sjoblom

Peo that is exactly what I needed. It looks like it works!!! Thanks a
million to you Peo and also John.

 

[jaf]

Hi Marvin,
I tested it with today's date. 11-01-2003
It won't work with 11-1-2003 or 11-01-03 as a sheetname.

( you did change the !a1:a10 to p1428 like I forgot to?) :<)

I did write a function similar to Peo's, but the worksheet solution looked
easier.

Public Function PreviousDay()
myname = ActiveSheet.Name
targetsheet = Format(Application.WorksheetFunction.Max(Date - 1, Date - 2,
Date - 3, Date - 4, Date - 5), "mm-dd-yy")
PreviousDay = Chr(39) & Mid(targetsheet, 1, Len(targetsheet)) & Chr(39)
'Use from worksheet =max(indirect(previousday()&"!p1428"))
End Function

 

[Marvin Hlavac]

( you did change the !a1:a10 to p1428 like I forgot to?) :<)

I did change it and it did work well but it was dependent on the date. But
now everything is fine. Many thanks John.