Unsure of how to collate data on Excel across sheet using drop down lists

[nicolechung]

Hi! 

I would like to create a way to collate data across my work sheets. Ideally I would ;

• see the number of hours spent in each target section (Knowledge/Skills/Behaviours) totaled yearly on another sheet ('Total KSB Hours'),
• be able to see a breakdown of which individual targets (each target section has multiple subsections) need more work or where there has been too much time spent on other individual targets. 
• also I am having trouble conditional formatting across worksheets when it comes to drop down menus

I think I am unsure of how I would collate the data and need advice on if I need a Pivot Table or another tool and how I would format it.

Also I can create a drop down list (using named range) but I am unsure if it possible to add multiple dropdown in the 'Learning Outcomes/Duties/KSBs' column. If I can do it, then in theory it should be quite straight forward to see how many times the each individual target has been selected and then creating a sum on the 'Total KSB Hours' sheet. 

Hopefully this all makes sense, any questions please let me know. Thank you, I look forward to hearing some ideas. 

Using Windows 10 Enterprise, Microsoft 365 for Enterprise, Version 2102. 

[#2]

@nicolechung Do you really need the paper calendar-style view in five separate sheets? You already seem to build a list of activities that contain Date, Activity, Summary and Outcome. Add two more columns. One for the hours and one for the categories that you have in the legend.

Collect all data for all five years in one long list and run a pivot table off that one. Category in the Row field, Date (grouped by year) in the column field and hours (summed) in the Value field.

No need for complicated formulae.

[#3]

Hi @Riny_van_Eekelen, 

Thank you for your advice.

That's a really good idea the coloured categories which makes things much easier. 

However I am unsure of how to collate the data for the KSB, here the first screenshot is of the various KSBs which are listed then mentioned in the different years sheets. I was thinking that I could link the KSBs by listing them in the outcomes column and then use the outcomes column in order to create a more detailed breakdown of which KSBs have been achieved.  If that is possible. What do you think? 

Thanks Nicole 

[#4]

@nicolechung I merely focussed on the way how you collect actual activity data. A list is far easier to summarise than the calendar view (four columns per month, 12 months sid-by-side) that you have in your file. Not really sure what mean by "collate data for the KSB". I see the matrix that you have, I can read the text but I'm not really up to speed as to how this ties to all other data.

[#5]

Thanks @Riny_van_Eekelen 

The calendar and table were the original format given to us for tracking achievement of the KSB targets. 

Using the calendar you see which days you did activities that used one of the targeted KSBs, then the table is for recording what you did and which specific KSBs were used in doing that activity. 

So we are supposed to note down on the calendar the hours and type of activity; the coloured drop down box which has the SUM for total hours at the bottom of the calendar. 

You have helped solve this issue, thank you. 

Next to explain a little more about KSBs and how it ties in with what I need.

In the table is a column with a breakdown of the exact KSBs which is what I am trying to track and I think I need a Pivot table to do it but I am unsure of how to gather that data. An example: I do an activity which hits both K4, K5, B2 and S4 I would put that in the last column of the table, I thought I could make a named range using the 'KSBs' sheet which would make a drop down to select K4, K5, B2 and S4 then have it update in 'Total KSB Hours' to show how many times KSB was used.  However, the list doesn't work as each cell can only have one drop down list selection. But as you can see each activity can have multiple KSBs achieved.