SOLVED

Vlookup for multiple tables

Copper Contributor

I am attempting to find out how worker hours are allocated towards different 'Locations' and different 'Jobs'. At the bottom of my October tab I have for one worker 'Wesley Golonka' whom has done 133 hours so far this month. I want to be able to get a list of where these hours were allocated with repeats summing into one row. For example he has done practically all his work at location 'Fontenoy' with his job being 'Landscaping' there - I would then want an output of say 120 hours there and the other 13 be associated with a differnt job and location. I would want this for all my workers. 

 

I have managed to program this for one day on sheet 1 but I cant seem to stretch it so i can encapsulate the whole working month, i think my approach may be wrong, and a vlookup formula would be more suitable. Any help would be greatly appreciated!!!

4 Replies
best response confirmed by bencharnock (Copper Contributor)
Solution

@bencharnock 

 

You are, indeed, making a very fundamental (and, sadly, fairly common) mistake. By storing the raw data in the fashion you are using, you are actually interfering with Excel's amazing abilities to do the very thing you're wanting to do: produce a summary report where Excel does all the work.

 

I've re-arranged some of your raw data into a single database in order to show you how the Pivot Table will automatically produce the kind of summary you are wanting, from a single table. The fundamental advice: LET EXCEL DO THE HEAVY LIFTING.

 

I only did the first two weeks of october in order to illustrate, but once all those days' data were reassembled into a singe table, Pivot Table was able to produce the summary report you see. It probably isn't exactly what you want; actually, with a more complete database (adding the days from September, August--the whole year-- for example) Excel will start giving you the ability to collapse into weekly or monthly or quarterly summaries by project, by person. There's a lot of flexibility in how to array the data.  But you have to let Excel do the work of separating data by week, by person, etc.

 

You'll find videos on YouTube that can help understand the power of, as well as how easy it is to use, Pivot Tables. Here's an on-line resource as well. https://exceljet.net/excel-pivot-tables

 

@mathetes Thanks, big help. The spreadsheet was designed for ease of use so my dad can easily see who's assigned where - is there a way to programme it so the pivot table automatically collects data from the separate tables (they all have the same headers? Or maybe get the data to automatically collate onto another table from which the pivot is created from? 

@bencharnock 

 

For the time being, I am going to stand by my contention that in creating separate input tables for each day, you are perhaps making it easier for your Dad at present to see where each person is assigned, but in the long run you are inevitably getting in the way of true ease of use and even true clarity. 

 

[Just for the record, before you can come back here with the comment that your Dad is too old to relate to a different design, you need to understand that I'm turning 79 this coming week; I doubt your Dad is any further into his senior years. I think we old  folks are fully capable of understanding spreadsheets.]

 

So let me ask you this: WHO does the actual data entry, first of all? I'm going to assume it's not your Dad, but even if it is, I'd continue to advocate collecting all input data on a single table. It would take learning to differentiate between that step and the output.

 

Then, once all the data are collected in a single database, you can create some output ("dashboard" is the word often used) screens that give the "ease of use in seeing where people are assigned" aspect of it. Those are both variations on output, and easily structured.

 

The Pivot Table automatically gets the data from a table (rows added to an officially designated Excel Table are automatically included in revised reports. You do need to click on "Refresh Data"...and depending on how the pivot table is designed, you may need to set a filter for a different month when the calendar flips over.

 

A separate sheet or screen can be designed to show who's assigned where on any given date, extracting that data from the single database. The information does have to be entered, but once entered can be displayed, and the dashboard sheet could be designed to show assignments for the week, for the last two days, whatever. That's an output design issue to be discussed.

 

Just don't design your data collection (the input) to do anything other than collect data; making it serve that other purpose really does get in the way, no matter how "clear" it might be on days one and two.

1 best response

Accepted Solutions
best response confirmed by bencharnock (Copper Contributor)
Solution

@bencharnock 

 

You are, indeed, making a very fundamental (and, sadly, fairly common) mistake. By storing the raw data in the fashion you are using, you are actually interfering with Excel's amazing abilities to do the very thing you're wanting to do: produce a summary report where Excel does all the work.

 

I've re-arranged some of your raw data into a single database in order to show you how the Pivot Table will automatically produce the kind of summary you are wanting, from a single table. The fundamental advice: LET EXCEL DO THE HEAVY LIFTING.

 

I only did the first two weeks of october in order to illustrate, but once all those days' data were reassembled into a singe table, Pivot Table was able to produce the summary report you see. It probably isn't exactly what you want; actually, with a more complete database (adding the days from September, August--the whole year-- for example) Excel will start giving you the ability to collapse into weekly or monthly or quarterly summaries by project, by person. There's a lot of flexibility in how to array the data.  But you have to let Excel do the work of separating data by week, by person, etc.

 

You'll find videos on YouTube that can help understand the power of, as well as how easy it is to use, Pivot Tables. Here's an on-line resource as well. https://exceljet.net/excel-pivot-tables

 

View solution in original post