How to use list instead of hardcoding data from tabs to a summary page

[Markalon]

Hello all, any help would be greatly appreciated.

I have a spreadsheet that totals up hours from different sheets in the workbook to a summary sheet. It won't let me post a file or a picture so I'll try and explain the best I can...

On each of the employee specific sheets (e.g. Sheet 2 - NeilB) I have 10 columns:

Date - Job No. - Panel - Wire - Tag - PPT - Test - Program - Rew - Misc

I manually enter the date, job number and number of hours for each of the remaining columns. No calculations here. I have several employees sheets. All their hours get totaled up on the summary sheet...

I have 10 columns on the summary sheet (Sheet 1 - Summary):

Job No. - System - Panel - Wire - Tag - PPT - Test - Program - Rew - Misc ... columns A through J

Job number is manually entered as is the system. In the remaining columns (C-J) I have the following formula (this is the formula in cell C54):

=IF(SUMIFS(NeilB!$C:$C,NeilB!$B:$B,$A54)+SUMIFS(SteveJ!$C:$C,SteveJ!$B:$B,$A54)+SUMIFS(ScottG!$C:$C,ScottG!$B:$B,$A54)+SUMIFS(JoeS!$C:$C,JoeS!$B:$B,$A54)+SUMIFS(JeffC!$C:$C,JeffC!$B:$B,$A54)+SUMIFS(MikeG!$C:$C,MikeG!$B:$B,$A54)+SUMIFS(GregW!$C:$C,GregW!$B:$B,$A54)=0,"",SUMIFS(NeilB!$C:$C,NeilB!$B:$B,$A54)+SUMIFS(SteveJ!$C:$C,SteveJ!$B:$B,$A54)+SUMIFS(ScottG!$C:$C,ScottG!$B:$B,$A54)+SUMIFS(JoeS!$C:$C,JoeS!$B:$B,$A54)+SUMIFS(JeffC!$C:$C,JeffC!$B:$B,$A54)+SUMIFS(GregW!$C:$C,GregW!$B:$B,$A54)+SUMIFS(MikeG!$C:$C,MikeG!$B:$B,$A54))

As you can see, its already a very long formula. And each of the columns are adjusted with the column specific information.

When I add a new employee, I have to go in and manually update every formula in each column. It works, but what I'm trying to figure out is how to create a criteria list of the employees so I can standardize the formula so I only have to update the employee list instead of each of the formulas. I'm sure there must be a way to do something like this?

Thanks in advance.

 

[Mentor82]

Hi,
I suggest writing either a VBA macro to summarize throughout all the worksheets or developing a formulae with job no as its parameter to summarize what you need. In my opinion you will not finger out a dynamic formulae for that because of the fact that each employee = new worksheet. Excel does have a few 3D formulas which allows to do some calculation on a prowided scope of worksheets, however it does not support calculation of conditional summary on many worksheets.
If you're interested in having either a macro or developed a function to dynamically calculate what you need, give me a shout.
Kind regards,
Sebastian

 

[Toadstool]

I'd change your design. If you want to keep to just Functions and avoid VBA then it will only get more complex.

I try and avoid multiple tabs with effectively the same data (e.g. 12 month sheets, 8 Region sheets) so I would restructure what you have to be:

• A "Data" sheet where you enter the Employee name, date, System, etc.
• A "Reference" sheet with a table of Employees. I'd also have a Job No. table to validate entries and maybe the Customer name for that Job?
• A "Summary" page with your calculations based on selection of data from the "Data" sheet.

Here's a draft of each sheet:

	A	B	C	D	E	F	G	H	I	J	K	L
Emp	Date	Job No.	Misc
NeilB	A992
SteveJ	C822
ScottG	C455
NeilB	A992
NeilB	C822
ScottG	C455

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"] System[/TD]
[TD="align: center"] Panel[/TD]
[TD="align: center"] Wire[/TD]
[TD="align: center"] Tag[/TD]
[TD="align: center"] PPT[/TD]
[TD="align: center"] Test[/TD]
[TD="align: center"] Program[/TD]
[TD="align: center"] Rew[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]02-Aug-19[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]19[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]04-Aug-19[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]06-Aug-19[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]18[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]08-Aug-19[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]18[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]10-Aug-19[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]12-Aug-19[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]

</tbody>

Data​

	B	C	D	E
Employee	Job No.	Customer
NeilB	A992	ACME Widgets Corp
SteveJ	C455	Bodgit Mison LLC
ScottG	C822	Soo, Grabbit & Runn Lawyers
JoeS
JeffC
MikeG
GregW

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Reference​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Employee[/TH]
[TD="align: left"]=Table1[Employee][/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Job[/TH]
[TD="align: left"]=Table2[Job No. ][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C	D	E	F	G	H	I	J	K	L	M

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Employee[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]NeilB[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Job Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]A992[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Week Commencing[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]Mon, 5-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"] System[/TD]
[TD="align: center"] Panel[/TD]
[TD="align: center"] Wire[/TD]
[TD="align: center"] Tag[/TD]
[TD="align: center"] PPT[/TD]
[TD="align: center"] Test[/TD]
[TD="align: center"] Program[/TD]
[TD="align: center"] Rew[/TD]
[TD="align: center"] Misc[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]to[/TD]
[TD="align: right"]Sun, 11-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]77[/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=B3+6[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=SUMIFS(Data!D:D,Data!$A:$A,$B$1,Data!$C:$C,$B$2,Data!$B:$B,">="&$B$3,Data!$B:$B,"<="&$B$4)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]to
L4[/TH]
[TD="align: left"]---copy & paste as above---[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M4[/TH]
[TD="align: left"]=SUM(D4:L4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



So on "Summary" I can select employee NeilB from the LoV (no more effort than selecting the tab) and job A992 from the LoV then if I want a date range I enter into B3 and have B4 add 6 days for the week so see all hours for NeilB, A992 and 5-11 August.

If I enter * into the Employee it gets me all employees for that job & week. Enter * into Job and I get all jobs.

Adding a new Employee doesn't need any formulae changes, just add a name to the Employee table and enter the data into the "Data" sheet.