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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
Back
Top