referencing a cell in a sheet where the sheet name is a result

[Guest]

My workbook has sheets named for each day of the month, (March 1, March
2...). On March 2, I'd like to reference a cell on March 1 (and March 3
would reference March 2, etc. so I need a formula that will reference "DAY -
1" so that I can copy it to subsequent pages and they will reference the cell
in the prior day's spreadsheet). I can create the text "March 1" in a cell
in March 2, but how do I use that in a formula to reference the correct cell.

Thanks!
Tim

 

[Jason Morin]

Try:

=INDIRECT("'March "&RIGHT(CELL("filename",A1),2)-1&"'!A8")

This will return the value from A8 for the previous day.
Note that the wb must already be saved for this to work.

HTH
Jason
Atlanta, GA

 

[Myrna Larson]

If you want to put the name of the previous sheet in a cell, say B1, the
formula is

=INDIRECT("'"&B1&"'!A1")

If you don't want to specify the previous sheet's name somewhere on the sheet,
you could use a UDF written in VBA. If you paste the following code in a
Module in your workbook, you can use the function like this:

=PrevDate(A1)

where A1 is the cell you want on the sheet for the previous date.

Function PrevDate(aCell As Range) As String
Dim WSName As String
WSName = Format$(CDate(Application.Caller.Parent.Name) - 1, "mmmm d")
PrevDate = Worksheets(WSName).Range(aCell.Cells(1).Address).Value
End Function

 

[Gord Dibben]

Tim

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) 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

Say you have 5 sheets, sheet1 through sheet5.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

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

 

[Guest]

THANKS! You guys are awesome! I spent all day yesterday trying to figure
that out and with your help I got it to work in about 20 min. That is so
great!!
Tim