looking for a simple solution

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
129
Office Version
  1. 2016
I am building a rota and so have 52 work sheets titled week1, week2, etc...

each sheet is identical in layout, similar to below. Assume that cells here run from A1:O5

What I want to do is have all the dates update across the 52 sheets when I enter the 'start' date in week1

Currently I am just doing by d1=b1+1, f1=d1+1 and so on, then starting week2 with b1='week1'!n1+1 and so on but this is so time consuming.

Is there a simpler way?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]02-jun[/TD]
[TD]hrs worked[/TD]
[TD]03-jun[/TD]
[TD]hrs worked[/TD]
[TD]04-jun[/TD]
[TD]hrs worked[/TD]
[TD]05-jun[/TD]
[TD]hrs worked[/TD]
[TD]06-jun[/TD]
[TD]hrs worked[/TD]
[TD]07-jun[/TD]
[TD]hrs worked[/TD]
[TD]08-jun[/TD]
[TD]hrs worked[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]9-6[/TD]
[TD]8[/TD]
[TD]11-9[/TD]
[TD]8[/TD]
[TD]DO[/TD]
[TD][/TD]
[TD]DO[/TD]
[TD][/TD]
[TD]9-6[/TD]
[TD]8[/TD]
[TD]9-6[/TD]
[TD]8[/TD]
[TD]9-6[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
This can be done with an event macro.
Right click on the Week1 sheet tab and select View Code.
Copy and paste this into the pane on the right.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' limit to single cell
If Target.Count > 1 Then Exit Sub
' limit to specific cell
If Target.Address <> "$B$1" Then Exit Sub
' must be a date
If Not IsDate(Target.Value) Then Exit Sub

' Good to go
Dim wkNum As Long, theDay As Long, Dt As Date
    
Dt = Target.Value

Application.EnableEvents = False
Application.ScreenUpdating = False

For wkNum = 1 To 52
    For theDay = 1 To 7
        Sheets("Week" & wkNum).Cells(1, theDay * 2) = Format(Dt, "dd-mmm")
        Dt = Dt + 1
    Next theDay
Next wkNum
        
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
now when you change the Week1 B1 date all the dates should update.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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