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

M

Markalon

New Member
Joined
Aug 22, 2014
Messages
11
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):

Code: 
=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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
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
 
Upvote 0
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:

ABCDEFGHIJKL
EmpDateJob No. Misc
NeilBA992
SteveJC822
ScottGC455
NeilBA992
NeilBC822
ScottGC455

<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​

BCDE
EmployeeJob No.
Customer
NeilBA992ACME Widgets Corp
SteveJC455Bodgit Mison LLC
ScottGC822Soo, 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]


ABCDEFGHIJKLM

<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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
241
Peter_SSs
Peter_SSs
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
228
Peter_SSs
Peter_SSs
J
  • Question Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
342
JMC57
J
S
  • Question Question
VBA Copy range of data from multiple sheets & paste into single column of summary sheet
Replies
1
Views
649
Micron
M
Q
  • Solved
Sum from two different data sheets
Replies
2
Views
293
questforexcel
Q

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top