Forum Discussion
Unsure of how to collate data on Excel across sheet using drop down lists
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.
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
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.
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.