Pulling data from the previous sheet

  • Thread starter Thread starter Microsoft Communities
  • Start date Start date
M

Microsoft Communities

Is it possible to pull data from the previous sheet in a formula without
naming the sheet?
Every sheet has the same formats but each sheet represents a day of the
month.
These sheet names will change every month.

Example:
I have the date in cell s12 and I want to add 1 day on each sheet.
In cell d15 i have a number that I want to carry over to the next sheet in
cell e15.
 
I think you'll need a custom function for this:


Function PrevSheet()

PrevSheet = Sheets(ActiveSheet.Index - 1).Name & "!"

End Function


Use as below:

=INDIRECT(prevsheet() & "A1")

To return the previous sheet cell at the current address

=INDIRECT(prevsheet() & ADDRESS(ROW(),COLUMN()))

To return previous row:

=INDIRECT(prevsheet() & ADDRESS(ROW()-1,COLUMN()))

To return set row, same column

=INDIRECT(prevsheet() & ADDRESS(2,COLUMN()))


That should be enough to give you the general idea.


Note, the values will not update when you drag your sheet into a different location. You will need to press F9 or, if calculation is automatic, update or enter a value. update a value



If you need to know how to put it into a VBA module:

Press Alt-F11
Click Insert, Module
Paste the function.






Is it possible to pull data from the previous sheet in a formula
without naming the sheet?
Every sheet has the same formats but each sheet represents a day of
the month.
These sheet names will change every month.

Example:
I have the date in cell s12 and I want to add 1 day on each sheet.
In cell d15 i have a number that I want to carry over to the next
sheet in cell e15.
Click to expand...



--
 
This worked once and then stopped after I copied one sheet to another.
I have a sheet called "Temp" which is blank except for a couple of cells
along with a basic form for the user to complete.
This "Temp" sheet is copied to up to 31 other sheets with the names of a
dates "01-07-09" through "31/07/09".
As soon as I copy "01-07-09" to "02-07-09" I get a #REF in all cells that
has the new formula in them.
If I edit a cell in the first sheet the formula works but as soon as I edit
in the second sheet I get the #REF error on both sheets. Below is just one
of the formulas I am using:

'Adds 1 day to the date of the previous sheet.

=INDIRECT(prevsheet() & "W3")+1
 
The #REF! error indicates a problem referring to the the address
returned by the function. I am unable to reproduce the problem. However,
I modified the function so it should be a little more robust. Just copy
it in place of the previous formula.


Function PrevSheet()

Dim PSndx
If ActiveSheet.Index - 1 = 0 Then
'if first sheet, then
PSndx = Sheets.Count
'look at last sheet
Else
PSndx = ActiveSheet.Index - 1
'look at previous sheet
End If

If Sheets.Count > 1 Then
PrevSheet = Sheets(PSndx).Name & "!"
Else
' only one sheet
PrevSheet = ""
'return nothing
' look at current sheet
End If

End Function


If you still experience problems, entering only =prevsheet() in a vacant
cell near the problem formula should tell you what it is returning. You
would need to interpret this with knowledge of the operation of the
Indirect function. Either or both of the following may help if you don't
understand what indirect is doing:

http://www.contextures.com/xlFunctions05.html#Arguments
http://www.cpearson.com/excel/indirect.htm

However, to put it in simple language, Indirect is returning the value
from the cell referenced in its argument. =Indirect("Sheet1!A1") will
return the value from Sheet1 cell A1.

If Cell B2 (same sheet as the formula) contains "Sheet1!A1" (no need for
the quotes) =Indirect(B2) will return the value from Sheet1 cell A1.

Getting back on track, if you still have problems, you need to be able
to check that prevsheet() is returning something and that the something
it returns forms a valid address when combined with the balance of your
Indirect argument.



--
Lil



This worked once and then stopped after I copied one sheet to another.
I have a sheet called "Temp" which is blank except for a couple of
cells along with a basic form for the user to complete.
This "Temp" sheet is copied to up to 31 other sheets with the names of
a dates "01-07-09" through "31/07/09".
As soon as I copy "01-07-09" to "02-07-09" I get a #REF in all cells
that has the new formula in them.
If I edit a cell in the first sheet the formula works but as soon as I
edit in the second sheet I get the #REF error on both sheets. Below is
just one of the formulas I am using:

'Adds 1 day to the date of the previous sheet.

=INDIRECT(prevsheet() & "W3")+1
Click to expand...



--
 
Using the new function I get the following results :

=INDIRECT(prevsheet() & "W3")+1

Result = "13-07-09!W3"
This is the sheet name and cell address.
 
Liliana said:
The #REF! error indicates a problem referring to the the address
returned by the function. . . .
Click to expand...

Technically, your udf returns a worksheet name followed by an
exclamation point.
I am unable to reproduce the problem. . . .
Click to expand...
....

Really?! The OP's problem is immediately reproducible. Did you try
using worksheet names like the OP indicated? That is, worksheet names
that look like dates in dd-mm-yy format.

Excel *REQUIRES* single quotes around worksheet names containing
hyphens, spaces, commas, slashes, etc. Safest *ALWAYS* to enclose
worksheet names in single quotes since Excel ignores them when they're
unnecessary but chokes when they're necessary but not provided.

Personally, I think something like


Function wso(r As Range, Optional ByVal n As Long = 0) As Variant
With r.Parent.Parent.Worksheets
n = n + r.Parent.Index

If n >= 1 And n <= .Count Then
Set wso = .Item(n).Range(r.Address)

Else
wso = CVErr(xlErrRef)

End If

End With

End Function


would be more useful, where the usage would be something like

=wso(A5,-1)

to return the value of the A5 cell in the preceding worksheet. This
also allows more flexibility since, e.g., the formula

=wso(SheetX!A5,-2)

would return the value of the A5 cell in the worksheet 2 before SheetX
which could be anywhere with respect the worksheet in which you're
entering this formula. This also makes it easier to use fully or
partially relative addresses, and it avoids the volatile INDIRECT
function.

This doesn't include wrapping. That's intentional on my part.
 
With this I get the following error.
I have the format for both cells an number no dec.
The results I get in the function Arguments box is the correct number 106
without the + and 107 adding + 1

=wso(W3+1)

#VALUE!
 
Maybe you wanted:
=wso(W3)+1


Microsoft said:
With this I get the following error.
I have the format for both cells an number no dec.
The results I get in the function Arguments box is the correct number 106
without the + and 107 adding + 1

=wso(W3+1)

#VALUE!
Click to expand...
 
My apologies to ED.

Comments for Harlan in line below.



exclamation point.
Really?! The OP's problem is immediately reproducible. Did you try
using worksheet names like the OP indicated? That is, worksheet names
that look like dates in dd-mm-yy format.
Click to expand...

Yes, really. Worked back of WB to front AND did not use date format tab
names - relied on memory. The reason might sound like an excuse so it is
best left unsaid.

Having acted in haste, will now mix my metaphors, eat a good does of
humble pie and repent at leisure.

For me, the loud clear message from your message is that my belief that
I still remember the things that came with ease before retirement is
incorrect. Using Activesheet instead of Caller and missing date
formatted tab names in one message is beyond embarrassing for someone
who always self-criticised even the smallest error in her own work - a
99% correct spreadsheet is not 100% correct.

Dignity gone, Confidence gone. About all I can walk away with is the
knowledge that I did remember correctly that no inbuilt function does
what Ed required.

Thank you for your intervention.
 
Microsoft Communities said:
With this I get the following error.
I have the format for both cells an number no dec.
The results I get in the function Arguments box is the correct number 106
without the + and 107 adding + 1

=wso(W3+1)

      #VALUE!
Click to expand...
....

You need to use the 2nd argument, and ALL the function does is return
the value from the other cell, so you need to add the value to the
function's result, NOT to the function's argument. So try

=wso(W3,-1)+1

If you want the function to default to the previous worksheet, change
its 1st line to

Function wso(r As Range, Optional ByVal n As Long = -1) As Variant
 
Back
Top