The following images show a sample workbook. I would like to link cell B2 from the Monday sheet, then from the Tuesday, Wednesday, Thursday and Friday sheets automatically into the Summary sheet in cell B2, then C2, etc. Any help would be appreciated.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Link same cell from separate workbooks
- Thread starter salianne
- Start date
there are a couple of ways to do it, one involves putting formula into each cell on your summary sheet , the other involves writing VBA code to parse all the sheets and load the data into your summary sheet.
Simply putting =Mon!B2 into your Summary Sheet in cell B2 will get you the reference for that cell. Do the same across the first row until you have them as far as =Friday!F2
Then highlight the five and copy them down to your last row will be quickest ..
Rgds
Rob
Simply putting =Mon!B2 into your Summary Sheet in cell B2 will get you the reference for that cell. Do the same across the first row until you have them as far as =Friday!F2
Then highlight the five and copy them down to your last row will be quickest ..
Rgds
Rob
Upvote
0
dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi salianne,
I have built timesheets for a busy logistics warehouse which uses lots of temps, so each agency needs a timesheet split by warehouse etc.
My advice to you is not to have a tab per day at all, as this makes recording hours for a time-sheet more complex than it needs to be.
Instead, add a column per day into one worksheet, using the date as the header. In my example below, I insert a date into C1 using the following code assigned to the button InsDte
Then, each day in H1, M1, R1, W1, AB1 and AG1 are formulas e.g., Cell H1 =C1+1 ; Cell M1 =H1+1
The user of the time-sheet adds the start and finish times e.g., 08:00 ; 17:00 etc (which are left blank above as it's a template file).
How I do it, I have columns (shown hidden in the above image for E, F and G) that calculate hours worked, total before lunch, and hours for that day:
(I have macros hide [H] or unhide these hidden columns, and even have a button to re-insert the formulas [F] incase someone messes with them).
Here, I'll show those formulas. Note, the ranges are replaced by named ranges, which I will also show further down).
Note, in this formula, any shift over 6 hours gets a 30 min lunch break (-0.5)
Then, scrolling to the right past all the columns for all days of the week, there are three columns for Total hours, standard hours, and overtime hours.
Here, overtime is calculated as more than 40 hours per week.
Notice that I have a dynamic table around my data which permits the named ranges to refer to whole columns e.g., Range("TimeSheetTable[Start (Fri)]") specifically refers to Range("AG2:AG60").
Being able to create named ranges shortens formulas and gives the elements in formulas semantic meaning to you, thus helping you construct the formulas.
In the Summary worksheet, you can then insert a pivot table, and use the pivot table functionality to create whatever summary you require.
Just to show you the pivot table values box (bottom right) where the hours summary columns are pulled...
Using pivot tables is a learned skill, and the knowledge of how to use pivot tables can be assimilated here:
Intro to Pivot Tables and Dashboards Video Series 1 of 3
Intro to Pivot Tables and Dashboards Video Series 2 of 3
Intro to Pivot Tables and Dashboards Video Series 3 of 3
Automate Your Pivot Table With VBA [Step By Step Guide]
The VBA Guide To Excel Pivot Tables
I'm using formulas above the pivot table to pull in the days of the week from the first table
I realise that this is a little hard to follow, but trust me, using pivot tables will pay dividends in many different scenarios you might come across in business, and these can also be controlled by VBA macros.
Hope that gives you some ideas,
Kind regards,
Doug.
I have built timesheets for a busy logistics warehouse which uses lots of temps, so each agency needs a timesheet split by warehouse etc.
My advice to you is not to have a tab per day at all, as this makes recording hours for a time-sheet more complex than it needs to be.
Instead, add a column per day into one worksheet, using the date as the header. In my example below, I insert a date into C1 using the following code assigned to the button InsDte
VBA Code:
Sub insertdate()
' insertdate Macro
Worksheets("Proman Time Sheet").Activate
Range("C1:D1").ClearContents
Range("C1:D1").Formula = "=TODAY()-WEEKDAY(TODAY(),1)"
Range("C1:D1").Copy
Range("C1:D1").PasteSpecial xlPasteValues
End SubThen, each day in H1, M1, R1, W1, AB1 and AG1 are formulas e.g., Cell H1 =C1+1 ; Cell M1 =H1+1
The user of the time-sheet adds the start and finish times e.g., 08:00 ; 17:00 etc (which are left blank above as it's a template file).
How I do it, I have columns (shown hidden in the above image for E, F and G) that calculate hours worked, total before lunch, and hours for that day:
(I have macros hide [H] or unhide these hidden columns, and even have a button to re-insert the formulas [F] incase someone messes with them).
Here, I'll show those formulas. Note, the ranges are replaced by named ranges, which I will also show further down).
Note, in this formula, any shift over 6 hours gets a 30 min lunch break (-0.5)
Then, scrolling to the right past all the columns for all days of the week, there are three columns for Total hours, standard hours, and overtime hours.
Here, overtime is calculated as more than 40 hours per week.
Notice that I have a dynamic table around my data which permits the named ranges to refer to whole columns e.g., Range("TimeSheetTable[Start (Fri)]") specifically refers to Range("AG2:AG60").
Being able to create named ranges shortens formulas and gives the elements in formulas semantic meaning to you, thus helping you construct the formulas.
In the Summary worksheet, you can then insert a pivot table, and use the pivot table functionality to create whatever summary you require.
Just to show you the pivot table values box (bottom right) where the hours summary columns are pulled...
Using pivot tables is a learned skill, and the knowledge of how to use pivot tables can be assimilated here:
Intro to Pivot Tables and Dashboards Video Series 1 of 3
Intro to Pivot Tables and Dashboards Video Series 2 of 3
Intro to Pivot Tables and Dashboards Video Series 3 of 3
Automate Your Pivot Table With VBA [Step By Step Guide]
The VBA Guide To Excel Pivot Tables
I'm using formulas above the pivot table to pull in the days of the week from the first table
I realise that this is a little hard to follow, but trust me, using pivot tables will pay dividends in many different scenarios you might come across in business, and these can also be controlled by VBA macros.
Hope that gives you some ideas,
Kind regards,
Doug.
Last edited:
Upvote
0
Solution
Excellent Doug. This looks like just what I need. I will change the layout of my workbook to example you have shown. Really appreciate this. I use Pivot Tables regularly.
Upvote
0
dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
You're welcome.
In case you know VBA, this is the code to replace formulas should someone delete a cell by accident:
VBA Code:
Sub PromanTimesheetFormulasInsert()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Worksheets("Proman Time Sheet").Activate
'SaturdayFormuula
Range("TimeSheetTable[Hours/MinsWorked]").ClearContents
Range("TimeSheetTable[Hours/MinsWorked]").Formula = "=IF(Employee="""","""",IF(StSat>EndSat,EndSat+1-StSat,EndSat-StSat))"
Range("TimeSheetTable[TotalBeforeLunch]").ClearContents
Range("TimeSheetTable[TotalBeforeLunch]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(SatHrWkd)+(MINUTE(SatHrWkd)*60)+(HOUR(SatHrWkd)*3600))/3600,""0.00"",(SECOND(SatHrWkd)+(MINUTE(SatHrWkd)*60)+(HOUR(SatHrWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Saturday]").ClearContents
Range("TimeSheetTable[Hours for Saturday]").Formula = "=IF(Employee="""","""",IF(SatTBL=0,0,IF(SatTBL>6,(SatTBL-0.5),SatTBL)))"
'SundayFormula
Range("TimeSheetTable[Finish Time]").ClearContents
Range("TimeSheetTable[Finish Time]").Formula = "=IF(Employee="""","""",IF(SunSt>SunEnd,SunEnd+1-SunSt,SunEnd-SunSt))"
Range("TimeSheetTable[Total Before Lunch]").ClearContents
Range("TimeSheetTable[Total Before Lunch]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(SunHrWkd)+(MINUTE(SunHrWkd)*60)+(HOUR(SunHrWkd)*3600))/3600,""0.00"",(SECOND(SunHrWkd)+(MINUTE(SunHrWkd)*60)+(HOUR(SunHrWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Sunday]").ClearContents
Range("TimeSheetTable[Hours for Sunday]").Formula = "=IF(Employee="""","""",IF(SunTBL=0,0,IF(SunTBL>6,(SunTBL-0.5),SunTBL)))"
'MondayFormula
Range("TimeSheetTable[Finish Time6]").ClearContents
Range("TimeSheetTable[Finish Time6]").Formula = "=IF(Employee="""","""",IF(MonSt>MonEnd,MonEnd+1-MonSt,MonEnd-MonSt))"
Range("TimeSheetTable[Total hours not inc. lunch]").ClearContents
Range("TimeSheetTable[Total hours not inc. lunch]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(MonHrsWkd)+(MINUTE(MonHrsWkd)*60)+(HOUR(MonHrsWkd)*3600))/3600,""0.00"",(SECOND(MonHrsWkd)+(MINUTE(MonHrsWkd)*60)+(HOUR(MonHrsWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Monday]").ClearContents
Range("TimeSheetTable[Hours for Monday]").Formula = "=IF(Employee="""","""",IF(MonTBL=0,0,IF(MonTBL>6,(MonTBL-0.5),MonTBL)))"
'TuesdayFormula
Range("TimeSheetTable[Finish Time9]").ClearContents
Range("TimeSheetTable[Finish Time9]").Formula = "=IF(Employee="""","""",IF(TuesSt>TuesEnd,TuesEnd+1-TuesSt,TuesEnd-TuesSt))"
Range("TimeSheetTable[TotalBeforeLunch10]").ClearContents
Range("TimeSheetTable[TotalBeforeLunch10]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(TuesHrsWkd)+(MINUTE(TuesHrsWkd)*60)+(HOUR(TuesHrsWkd)*3600))/3600,""0.00"",(SECOND(TuesHrsWkd)+(MINUTE(TuesHrsWkd)*60)+(HOUR(TuesHrsWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Tuesday]").ClearContents
Range("TimeSheetTable[Hours for Tuesday]").Formula = "=IF(Employee="""","""",IF(TuesTBL=0,0,IF(TuesTBL>6,(TuesTBL-0.5),TuesTBL)))"
'WednesdayFormula
Range("TimeSheetTable[Finish Time13]").ClearContents
Range("TimeSheetTable[Finish Time13]").Formula = "=IF(Employee=0,"""",IF(WedSt>WedEnd,WedEnd+1-WedSt,WedEnd-WedSt))"
Range("TimeSheetTable[TotalBeforeLunch14]").ClearContents
Range("TimeSheetTable[TotalBeforeLunch14]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(WedHrsWkd)+(MINUTE(WedHrsWkd)*60)+(HOUR(WedHrsWkd)*3600))/3600,""0.00"",(SECOND(WedHrsWkd)+(MINUTE(WedHrsWkd)*60)+(HOUR(WedHrsWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Wednesday]").ClearContents
Range("TimeSheetTable[Hours for Wednesday]").Formula = "=IF(Employee="""","""",IF(WedTBL=0,0,IF(WedTBL>6,(WedTBL-0.5),WedTBL)))"
'ThursdayFormula
Range("TimeSheetTable[Finish Time17]").ClearContents
Range("TimeSheetTable[Finish Time17]").Formula = "=IF(Employee="""","""",IF(ThurSt>ThurEnd,ThurEnd+1-ThurSt,ThurEnd-ThurSt))"
Range("TimeSheetTable[TotalBeforeLunch18]").ClearContents
Range("TimeSheetTable[TotalBeforeLunch18]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(ThurHrsWkd)+(MINUTE(ThurHrsWkd)*60)+(HOUR(ThurHrsWkd)*3600))/3600,""0.00"",(SECOND(ThurHrsWkd)+(MINUTE(ThurHrsWkd)*60)+(HOUR(ThurHrsWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Thursday]").ClearContents
Range("TimeSheetTable[Hours for Thursday]").Formula = "=IF(Employee="""","""",IF(ThursTBL=0,0,IF(ThursTBL>6,(ThursTBL-0.5),ThursTBL)))"
'FridayFormula
Range("TimeSheetTable[Finish Time21]").ClearContents
Range("TimeSheetTable[Finish Time21]").Formula = "=IF(Employee="""","""",IF(FriSt>FriEnd,FriEnd+1-FriSt,FriEnd-FriSt))"
Range("TimeSheetTable[TotalBeforeLunch22]").ClearContents
Range("TimeSheetTable[TotalBeforeLunch22]").Formula = "=IF(Employee="""","""",IF(ISERROR(SECOND(FriHrsWkd)+(MINUTE(FriHrsWkd)*60)+(HOUR(FriHrsWkd)*3600))/3600,""0.00"",(SECOND(FriHrsWkd)+(MINUTE(FriHrsWkd)*60)+(HOUR(FriHrsWkd)*3600))/3600))"
Range("TimeSheetTable[Hours for Friday]").ClearContents
Range("TimeSheetTable[Hours for Friday]").Formula = "=IF(Employee="""","""",IF(FriTBL=0,0,IF(FriTBL>6,(FriTBL-0.5),FriTBL)))"
'SummaryFormulas
Range("TimeSheetTable[Total]").ClearContents
Range("TimeSheetTable[Total]").Formula = "=IF(Employee="""","""",SUM(SatHours+SunHours+MonHours+TuesHours+WedHours+ThurHours+FriHours))"
Range("TimeSheetTable[Standard]").ClearContents
Range("TimeSheetTable[Standard]").Formula = "=IF(Employee="""","""",TotalHours-OvertimeHours)"
Range("TimeSheetTable[Overtime]").ClearContents
Range("TimeSheetTable[Overtime]").Formula = "=IF(Employee="""","""",IF(TotalHours>40,TotalHours-40,0))"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
Upvote
0
Lol, I hate VBA, but can copy copy and use it ok as long as someone else writes. Thank you for all your help.
Upvote
0
Similar threads
- Solved
