Multiple timesheets in tabular form to list form?

[rubinrods]

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

 

[CatyH]

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]

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]

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]

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]

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]

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!