Insert a value from the previous sheet in excel

[Guest]

In writing a pilots logbook spreadsheet with separate sheets for each month,
how do I carry forward values from one month to the next without having to
type the name of the last month in the formula bar? Is there proword for a
cell in the previous sheet?

 

[Frank Kabel]

Hi
how have you named your worksheets?

 

[Jason Morin]

If your sheets are named Jan, Feb, Mar, Apr, etc., try:

=INDIRECT(TEXT((MID(CELL("filename",A1),FIND("]",CELL
("filename",A1))+1,999)&"2004")*1-1,"mmm")&"!A1")

This will return the value from cell A1 from the previous
month.

HTH
Jason
Atlanta, GA

 

[Gord Dibben]

Bob

Willing to go with a User Defined Function?

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) in any cell on sheet2 and you'll get B2 from sheet1.
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

When you get the UDF stored in a module in your workbook, just select
February sheet then SHIFT + select December sheet.

In a cell on February sheet enter =PREVSHEET(cellref)

All sheets except the first will get the formula entered.

Ungroup the sheets.

Gord Dibben Excel MVP