STUCK! read cell from previous page (s)

kevrobertson

New Member
Joined
Apr 10, 2003
Messages
8
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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"}))))
 
Upvote 0
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)

?
Rich (BB code):
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
ABC
1
2M30 value
3
4
5ax
6dbb
7x
8e
Sheet5
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top