Summing Data From an External Data Table

[David Washbrook]

I am wanting to get the sum of hours planned to be worked for a week by our tradespeople. The data is in an external spreadsheet which has a row for each of the tradespeople and the column headings are the days of the year. The cells in this table contain the hours planned to be worked on each day, however on days off the cell contents often contain text rather than the number 0. I will be specifying the week start date in my spreadsheet and in one column I have the names of the tradespeople I want to report on. In the next column I want to be able to show the sum of the hours planned for that week for each person. Each week I will change the week start date to produce a fresh report. Any tips on the best way to go about this?

 

[Vishal Srivastava]

Hi David,
Welcome to the forum!

I would like to suggest you to build sample format with expected sample result first.

Thanks & Regards,
CMA Vishal Srivastava

 

[Chihiro]

Personally, I'd redesign how the data is stored. Instead of days of year as column, I'd put it in rows and structure source data like database table.

It would make later analysis much easier, as well as make transfer between different BI tools smooth.

If the data is structured as table with columns like below...
Column headers - Name; Date; Hr Worked; etc

You can use PowerQuery and other built in tools to pull the info and make a quick report.

 

[David Washbrook]

Personally, I'd redesign how the data is stored. Instead of days of year as column, I'd put it in rows and structure source data like database table.

It would make later analysis much easier, as well as make transfer between different BI tools smooth.

If the data is structured as table with columns like below...
Column headers - Name; Date; Hr Worked; etc

You can use PowerQuery and other built in tools to pull the info and make a quick report.

Thank you for the reply Chihiro. I fully agree with your recommendations but unfortunately I am not the owner of the data. I have since worked it out using SUM, INDEX and MATCH functions. I am fairly new to these and had made a basic mistake when I first tried to apply them. I got out of the office, went for a run and with a clear head the answer came to me.