STUCK! read cell from previous page (s)

[kevrobertson]

Morning from Scotland,

Intermediate level excel user.

I use excel to organise my staff and to give them their rotas weekly.
So I have a workbook with 58 tabs.
The problem I am having is that I would like to have a cell from 1 week read from a cell in the previous week. I can obviously use the formula =('10th June'!M30) for a specific sheet but the formula I would like to use would be something like =('PREVIOUS SHEET'!M30) so that I can duplicate sheets and it always refers to previous sheet other than an absolute manual solution.

Any help greatly received.

Thanks

Kevin

 

[Special-K99]

1. Exactly one week ago or something different?
2. The cell will always be M30. Is this correct?

The formula would use the INDIRECT() function which if copied hundreds/thousand of times could slow the worksheet down.

 

[kevrobertson]

yes exactly 1 week ago
and cell will always be the same M30

off to look up indirect....

thank you

1. Exactly one week ago or something different?
2. The cell will always be M30. Is this correct?

The formula would use the INDIRECT() function which if copied hundreds/thousand of times could slow the worksheet down.

 

[Special-K99]

Think you need

=INDIRECT(SUBSTITUTE(TEXT(TODAY()-7,"'ddxx mmm")&"'!M30","xx",LOOKUP(DAY(TODAY()-7){1,2,3,4,21,22,23,24,31},{”st”,”nd”,”rd”,”th”,”st”,”nd”,”rd”,”th”,”st”})))

NOTE: This based on every sheet having a two digit format for the day along with an ordinal indicator (st, nd, rd, th) and a month spelt in full
If the day is only one digit when 1st-9th then change the format to "'dxx mmm"

The "xx" is to shorten the format which then gets replaced with the oridnal indicator

 

[Special-K99]

That formula should be

=INDIRECT(SUBSTITUTE(TEXT(TODAY()-7,"'ddxx mmm")&"'!M30","xx",LOOKUP(DAY(TODAY()-7),{1,2,3,4,21,22,23,24,31},{"st","nd","rd","th","st","nd","rd","th","st"}))))

 

[Peter_SSs]

I would like to have a cell from 1 week read from a cell in the previous week. I can obviously use the formula =('10th June'!M30) for a specific sheet but the formula I would like to use would be something like =('PREVIOUS SHEET'!M30)

Another way would be to invoke a user-defined function, provided vba is acceptable. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

?

Function ShOffset(SheetOffset As Long, CellAddress As String) As Variant
  Dim indx As Long
  
  indx = Application.Caller.Parent.Index + SheetOffset
  If indx < 1 Or indx > Sheets.Count Then
    ShOffset = CVErr(xlErrRef)
  Else
    ShOffset = Sheets(Application.Caller.Parent.Index + SheetOffset).Range(CellAddress).Value
  End If
End Function

Excel Workbook
	A	B	C
1
2		M30 value
3
4
5	a	x
6	d	bb
7	x
8	e
Sheet5

 

[kevrobertson]

thank you so much,
Works a treat

Kevin

 

[Peter_SSs]

thank you so much,
Works a treat

Kevin

Not sure which suggestion you are referring to but glad you got something that suits.

 

[kevrobertson]

Not sure which suggestion you are referring to but glad you got something that suits.

Yours I knew the easiest way would be VBA, but still a beginner in that.

Thanks again

Kevin

 

[Peter_SSs]

I knew the easiest way would be VBA, ..

Though of course the 'easiness' of a particular solution depends largely on your own individual skills & experience.