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
1	Emp	Date	Job No.	System	Panel	Wire	Tag	PPT	Test	Program	Rew	Misc
2	NeilB	02-Aug-19	A992	16	11	21		17		2		19
3	SteveJ	04-Aug-19	C822	3	19	10	3	4	11	5		1
4	ScottG	06-Aug-19	C455	5		13	0				7	18
5	NeilB	08-Aug-19	A992	20	5		21	1	12			18
6	NeilB	10-Aug-19	C822	10	11		16	16	15		15	1
7	ScottG	12-Aug-19	C455	15	7	19	11	20	4		10	8

<tbody>
</tbody>

Data​

	B	C	D	E
1	Employee		Job No.	Customer
2	NeilB		A992	ACME Widgets Corp
3	SteveJ		C455	Bodgit Mison LLC
4	ScottG		C822	Soo, Grabbit & Runn Lawyers
5	JoeS
6	JeffC
7	MikeG
8	GregW

<tbody>
</tbody>

Reference​

Workbook Defined Names Name Refers To Employee =Table1[Employee] Job =Table2[Job No. ] <tbody> </tbody>

<tbody>
</tbody>

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee	NeilB
2	Job Number	A992
3	Week Commencing	Mon, 5-Aug-19		System	Panel	Wire	Tag	PPT	Test	Program	Rew	Misc	Total
4	to	Sun, 11-Aug-19		20	5	0	21	1	12	0	0	18	77

<tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula B4 =B3+6 D4 =SUMIFS(Data!D:D,Data!$A:$A,$B$1,Data!$C:$C,$B$2,Data!$B:$B,">="&$B$3,Data!$B:$B,"<="&$B$4) to L4 ---copy & paste as above--- M4 =SUM(D4:L4) <tbody> </tbody>

<tbody>
</tbody>

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.