I have created a project and hours tracking spreadsheet in Excel. There is a Overview page that collects data from each of the project sheets (I have attached a screenshot for reference). As I enter the hours on the project sheets they combine together on the overview for that person for that day.


I am looking for a way to choose a cell on the overview sheet and find all of the sheets that has time on them that are for that person on that day(where that time came from). I only want to return that have a value in them. The same person may work on multiple projects in a single day and there time is broken up over those projects.

Any help would be greatly appreciated. I will try to answer any questions as quickly and detailed as possible. Thank you in advance.

Attached Images

Time Sheet.jpg (178.7 KB, 35 views)

You would be far better to attach the workbook rather than a picture of the workbook.

Thank you for the advice. I made a simple workbook using the same concept that we use in our Time keeping workbook. Maybe this will help.

Attached Files

Time Sheet Example.xlsx (38.9 KB, 5 views)

A thought I had about this:

Is there a way to generate a Dynamic Note/Comment in the cell that would display the sheet names that have values that are contributing to the cell data?

I can do something like this which shows all the time where data has come from:
At the moment it doesn't show names or weekdays which have no data.

2024-07-31_150505.jpg

and if you double-click on a cell with hours in you get a separate sheet with the likes of:
2024-07-31_150643.jpg

It's based on a pivot table which is in turn based on consolidated data from your job# sheets.

Any use?

Yes I think I can make that work. I am unfamiliar with making a pivot table. That being said, Can you send me back the the excel file with your pivot table in it so i can see how you built it. Thank you

In the attached:
1. The job # sheets' data have all been converted to proper Excel tables with the table names all starting with JobNo. This is important because the Power Query query ignores any other tables you might have. If you have new data to add to any of these sheets (new rows or columns) then that new data must be included in the tables. Any new job sheets you might add should also have their data converted to Tables and those tables' names should start with JobNo.
2. There's a Power Query query which consolidates these tables and transforms the data into a form suitable for creating a Pivot table from. The PQ query actually outputs directly to the pivot table.
3. It's important to know that the data is added to the Data Model, since this allows the generation of the WhereFrom columns.
4. It's normal built-in behaviour of a pivot table to be able to double-click on a cell which shows hours, for it to produce a new sheet showing the origin of the data in the cell you double-clicked on.
5. Pivot tables need refreshing if the data they're based on changes. You can do this by clicking on the Refresh all icon in the Queries & Connections section of the Data tab or by right-clicking the pivot table and choosing Refresh.


If you need more info, comeback.

Attached Files

MSOfficeforums52698Time Sheet Example.xlsx (153.7 KB, 3 views)

Thank you very much. I will take a look at it and do some research on pivot tables to help me better understand what is going on. Thank you again.