Multiple timesheets in tabular form to list form?

rubinrods

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Here is what I have...

A supervisor updates 1 timesheet for every day of the week (7 total)
Each time sheet is saved under the same sheet stacked vertically (see sheet picture)
At the end of the week the data from these 7 individual timesheets must be summarized as a list of records on another sheet (same workbook)

Up to now the summary has been filled out manually and takes too much time

I have experimented with power query by creating another sheet that combines the headers to the right of the employee names and then unpivoting the data. This worked to an extent but the data in the headers is dynamic and changes from time to time. So when these headers are changed the power query could not find the columns and it would need to be connected and linked again which also takes a lot of time. Then since there are 7 timesheets this also becomes time consuming.

The time sheets themselves can also be modified but the present data must still be captured.

I am very lost on this one. Any help or suggestions are appreciated
 

Attachments

  • monday timesheet.PNG
    monday timesheet.PNG
    33.3 KB · Views: 1
  • tuesday timesheet.PNG
    tuesday timesheet.PNG
    36.1 KB · Views: 0
  • stacked timesheets.PNG
    stacked timesheets.PNG
    13.6 KB · Views: 1
  • summary sheet.PNG
    summary sheet.PNG
    28.5 KB · Views: 1

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

CatyH

Board Regular
Joined
Jun 27, 2017
Messages
77
are you creating macros to pull the data or using formulas? Can you start by creating some way to pull in the dynamic headers, create that list, and use that to do you (i assume) sumifs, etc?
(also - i don't download images so I haven't actually seen your setup up close...)
 

CatyH

Board Regular
Joined
Jun 27, 2017
Messages
77
are you creating macros to pull the data or using formulas? Can you start by creating some way to pull in the dynamic headers, create that list, and use that to do you (i assume) sumifs, etc?
(also - i don't download images so I haven't actually seen your setup up close...)
For example, on your summary tab... can you:
have all your column headers be formulas that point to the original headers on the data entry screen
have all your row headers be formulas that point to the original row headers ... on monday... (assuming they are same for each day???)
then the contents of your summary could be SUMIFS (Sheet1!C:C, Sheet1!$A:$A, $A3) and then you can copy/paste that formula into your summary table?
 

rubinrods

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
are you creating macros to pull the data or using formulas? Can you start by creating some way to pull in the dynamic headers, create that list, and use that to do you (i assume) sumifs, etc?
(also - i don't download images so I haven't actually seen your setup up close...)
up to now the data was just being typed into a summary sheet by looking at the individual timesheets. I tried formulas as well but since data is pulled from the headers as well (these change) its been difficult. I believe that the easiest thing to do is to use a form that submits the time sheet data. so the supervisor would actually type in the information into the form that populates a running record
 

rubinrods

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

For example, on your summary tab... can you:
have all your column headers be formulas that point to the original headers on the data entry screen
have all your row headers be formulas that point to the original row headers ... on monday... (assuming they are same for each day???)
then the contents of your summary could be SUMIFS (Sheet1!C:C, Sheet1!$A:$A, $A3) and then you can copy/paste that formula into your summary tabl

since it is more than one timesheet it gets complicated
 

CatyH

Board Regular
Joined
Jun 27, 2017
Messages
77
since it is more than one timesheet it gets complicated
oh - so Tuesdays headers <> Monday's headers... that would make it tricky, yes. I could build something that would work but I'd want to use macros... and unfortunately I'm supposed to be working right now so probably shouldn't promise development time... ;) I just am addicted to helping people solve problems!
 

rubinrods

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
oh - so Tuesdays headers <> Monday's headers... that would make it tricky, yes. I could build something that would work but I'd want to use macros... and unfortunately I'm supposed to be working right now so probably shouldn't promise development time... ;) I just am addicted to helping people solve problems!

Yeah a macro probably makes more sense. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,340
Messages
5,624,106
Members
416,011
Latest member
chengkoonwing

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
Top