How can I reference a cell on a previous worksheet

[Kim]

I have set up a workbook where each tab is a new day (Aug1, Aug2, etc.)
On each worksheet, I want to bring forward readings from the previous day,
but I don't want to reference the previous sheet by name or I have to change
all of the formulas on each sheet. How can I reference say, the number in
cell A1 on the worksheet named Aug1, and put it into cell A1 on the worksheet
named Aug2, then A2 would carry to A3, etc. Basically, I need to tell it to
get readings from the previous day without referring to the tab name. Please
help!

 

[Browny]

I'm new at this but,
in A1 of AUG2 =CELL("CONTENTS", AUG1!A1) It refers to another sheet, not
sure if that's want you want.

 

[Héctor Miguel]

hi, Kim !

I have set up a workbook where each tab is a new day (Aug1, Aug2, etc.)
On each worksheet, I want to bring forward readings from the previous day
but I don't want to reference the previous sheet by name or I have to change all of the formulas on each sheet.
How can I reference say, the number in cell A1 on the worksheet named Aug1
and put it into cell A1 on the worksheet named Aug2, then A2 would carry to A3, etc.
Basically, I need to tell it to get readings from the previous day without referring to the tab name...

in the the following example it is required a "saved" workbook and two defined names with "ancient" xl4 macro-functions

1) put this formula on every worksheet (i.e.) in cell A1
[A1] =mid(cell("filename",a1),search("]",cell("filename",a1))+1,31)

2) (menu) insert / name / define...
name: formula:
a) Worksheets =mid(get.workbook(1+0*now()),search("]",get.workbook(1))+1,31)
b) Prev.Sheet =index(worksheets,max(1,match(!$a$1,worksheets,0)-1))

=> note the sign "!" in lieu of the whole worksheet name

3) "call" last name (prev.sheet) say in cell A2 on every worksheet
[A2] =prev.sheet

4) you could use =indirect(a2&"!b5") to get the value in cell B5 of the "previous sheet"

*IF* you move the index/position of your "current" sheet, the name (Prev.Sheet) will allways refers to previous sheet (index)

modify/adapt/... the use of the name according to your needs

hth,
hector.

 

[Lori]

To bring forward the previous sheet value in A1, try:

=NPV(2^-32-1,IF(1,'*'!A1))*2^-32^COUNT(IF(1,'*'!A1))

then fill down for the other values. Copy this formula from the formula bar
before pressing enter to paste to other sheets.

NB: The '*' will change to all sheets other than the active one but beware
if you're using Excel 2002 as there's a bug when editing these formulas. Also
the formula requires that the values are not too large.