referring to previous worksheet in workbook

[Freida]

Back to my timesheet workbook...

I add a new sheet for each pay period (every two weeks). I name each
timesheet with the last date of the period (current sheet is 10.27.07;
previous one is 10.13.07).

To keep track of the amount of leave time accumulated I have to use cell
values from the previous periods timesheet. It is not a specific sheet; it
is relative, i.e. always the sheet preceeding the current one).

Currently, I manually change those cells to reflect the correct timesheet
name. Is there a way to refer to the relative timesheet in excel?

 

[JE McGimpsey]

See

http://mcgimpsey.com/excel/udfs/prevsheet.html

 

[Gord Dibben]

Depends upon how you are adding a new sheet.

Are you copying and clearing constants from the copied sheet or just
Insert>Worksheet?

Do you have a worksheet template you use for inserting?

Post back and we'll come up with something automatic.

But here is a UDF that refers to the previous sheet.

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

In a cell enter =PrevSheet(A1) to return the contents of A1 from previous
sheet.

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

 

[Freida]

In answer to your questions:

Are you copying and clearing constants from the copied sheet or just
Insert>Worksheet? Using Insert>Worksheet...

Do you have a worksheet template you use for inserting? Template that I
made up.

As I mentionned in my first post about this timesheet, I am a self-taught
novice so bear with me while I make sure I understood properly so far:
I follow your directions to create the general module and then paste in the
code you gave me.
However, I think I was using the wrong terminology when I said "previous
sheet"..which it seems to me must refer to the one to the immediate left of
the current sheet. However I insert each new sheet on top of the previous
ones...which means I want to refer to the sheet to the immediate right of the
current one. Am I correct that I therefore have to replace all the n-1 with
n+1...and I probably also have to get rid of that error condition if n=1. So
would I be left with something like this:

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If TypeName(Sheets(n + 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n + 1).Range(rg.Address).Value
End If
End Function

I am really swimming in strange waters here...so please advise if I am
correct.

 

[Gord Dibben]

Changing the (n - 1) to (n + 1) seems to work to get data from sheet to right.

I would leave the error condition line, otherwise if there is no sheet to right
you will get a #VALUE! error in cell rather than the #REF! which may be
misleading.


Gord

 

[Freida]

I tried posting this earlier today but it has not shown up...so I am
reposting. If it is duplicate, I apologize.

I see your point about setting up something in the event of no sheet to the
right of the current sheet (which should only happen for the first sheet of
the year).

But since my current sheet is always on top, wouldn't the error of n= 1
always be triggered?

I was thinking of using a separate template for the first sheet of the year
(or just blanking out those fields in the first sheet)...but if there were a
way to determine if there was no sheet to the right of the current sheet, I
could use the If function to put the correct data in the first sheet
automatically. I would prefer this way, if it is possible.

 

[Gord Dibben]

but if there were a

way to determine if there was no sheet to the right of the current sheet, I
could use the If function to put the correct data in the first sheet
automatically

If no sheet to the right, and you had a formula of =PrevSheet(A1) from where
would the data come to be filled in automatically?


Gord

 

[Freida]

Because I would condition it with the If function. If there *was* a sheet
the cell would me =PrevSheet(A1) + 4.65; if there *was not* a sheet to the
right then =4.65

 

[Balkar]

I absolutly love this function, however I am having a problem that if I have
another workbook open at the same time, then it sometimes grabs data from
Sheets(n-1) from that workbook. I added Application.Volatile to the function
to have the function get the new data as soon as the pointed to cell changes.
Could this be what is causing the workbook confusion?

 

[Dave Peterson]

You have an active thread elsewhere.

I absolutly love this function, however I am having a problem that if I have
another workbook open at the same time, then it sometimes grabs data from
Sheets(n-1) from that workbook. I added Application.Volatile to the function
to have the function get the new data as soon as the pointed to cell changes.
Could this be what is causing the workbook confusion?