Hello,
Please see attached example of the type of spreadsheet I am trying to create.
I want to be able to copy and paste data into the CONTROL worksheet and then have the formulas in each of the tables below the graphs contained in the GRAPHS worksheet that will automatically update the charts everytime this new data is pasted into CONTROL.
Each graph is reporting on the same data, but in a different way. For example, in Fig 1. the formula in the 3rd column of the 'Actual' row will pick out the number of instances in the CONTROL worksheet where someone is recorded as a 'Director' (column G) with a score of '3' (column T). I would also like the formula to display this as a percentage value of the pool. E.g. 40% of 'Director' scored a competency score of '3'. The next column in the Actual row would do the same for all 'Director' who had a score of '4' etc.
In Fig 2. the fourth column of the 'Actual' row of the graph's table is will look for data that is recorded as belonging to the 'London' region (column J) and the 'Buildings Fabric' substream (column L) who have a score of '4'. Again, this would need to display as a percentage of that pool, e.g. 35% of London, Buildings Fabric employees belong have a score of '4'.
I've got a feeling it may be doable with a COUNTIFS formula (see below). Also the data pool will change each time this report will be generated so the end of the formula I have used below to get the percentage (/984) will not work if the pool is larger or smaller!
=COUNTIFS(Sheet5!$T$4:$T$987,Sheet5!$T$4,Sheet5!$G$4:$G$987,Sheet5!$G$4)/984
I hope that the above makes sense, but please ask me if you need further clarification.
Thanks
Bookmarks