Sheet offset function?

[Gavin]

I have a workbook with multiple sheets. On each sheet I want to retriev
a value from the sheet before without knowing the sheet names. ie. If
am on the 3rd sheet, I want a value from the 2nd sheet, etc. Is ther
any kind of sheet offset function? Or is there a function to lookup th
sheet index from which I can subtract 1?
Thanks
Gavi

 

[J.E. McGimpsey]

Check the archives:

http://google.com/advanced_group_search?q=group:*excel*


use "Previous sheet" (without quotes) as a search term.

 

[Don Guillett]

Try this
Sub minussheet()
MsgBox Sheets(Sheets(ActiveSheet.Name) _
..Index - 1).Range("b3")
End Sub

 

[Gavin]

Thanks for your responses guys, but I was looking for a Workshee
Function (formula) to solve my problem. After reviewing the archive
and doing much research I declare that there is no solution for this i
Excel (go, Microsoft!).

In any other spreadsheet package, if I type a formula referencin
another sheet and then copy the formula to a different sheet it adjust
the sheet reference accordingly, but Excel doesn't. (God knows why!)

I know how find out the sheet name in a function, but that doesn't hel
because it can't tell me the name of the previous sheet.

I know how to find out the sheet index using VBA, but that doesn't hel
because I'm not running a macro - I'm just copying formulas.

I know how to create a UDF to find out the sheet index and use it in m
formula, but that doesn't help because once the UDF has returned th
sheet index it doesn't automatically recalculate the index for th
other sheets. If the UDF decides the sheet index is, say "3", then i
returns "3" to every formula on every sheet.

I know something about including "application.volatile" in my UDF t
apparently recalculate the UDF every time a formula recalculates, bu
that doesn't work either - I don't know why, and niether does anyon
else judging by the lack of response to previous questions regardin
this matter (I'm not complianing - I'm just saying that if nobod
responds then I assumme nobody knows).

So here's a short message to everyone who ever searches the archive
for a solution on how to copy a formula to a different sheet an
maintain relative sheet references: IT CAN"T BE DONE IN EXCEL.

If you've read this far - thanks for the dedication
Gavin :-

 

[J.E. McGimpsey]

I'm not sure what you've tried before, but this UDF returns the
correct reference each time.

Public Function PrevSheet(Optional rRng As Excel.Range) As Variant
Dim ndx As Integer
Application.Volatile
If rRng Is Nothing Then Set rRng = Application.Caller
ndx = rRng.Parent.Index
If ndx > 1 Then
Set PrevSheet = Sheets(ndx - 1).Range(rRng.Address)
Else
PrevSheet = CVErr(xlErrRef)
End If
End Function

Entering

C1: =PrevSheet()

returns the value in the previous sheet's C1 cell (i.e., the
corresponding cell it's called from)

entering


C1: =PrevSheet(B10)

returns the value in the previous sheet's B10 cell regardless which
cell it's called from.

Calling from the first sheet in the workbook resuts in a #Ref! error.

 

[Peo Sjoblom]

If you use a naming system with numbers like abcd1 abcd2 and so on you can
use the cell function
It will return the sheet name of the sheet it is in and if you would be in
abcd3 you could extract
the number from the sheet name and then use it in a formula. An example
using abcd3 to return what is in
abcd2 cell a50


=INDIRECT("'"&LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3
2),4)&RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32),LEN(
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32))-4)-1&"'!A50")

Of course I would go with J.E.'s solution any day