Nov 23 2018 07:59 AM
I have a sharepoint list for colleague assessments. Each time they are assessed we rate our colleagues from 1-4 on each of their different tasks. this is done on sharepoint.
what i would like to do is ask excel to look at the lists and display the average rating for each colleague on each process.
I was thinking of something like a IF AND formula that says 'check the sharepoint list and if the colleagues name is X AND the process is Y, work out the average in the ratings column.
I could do this if i exported the list to sharepoint, but as far as i know, when i update the list, it wouldnt update the export and so i would need to keep exporting.
Is there a way to create this desired outcome live, with minimal maintenance?
TIA
Nov 23 2018 08:37 AM
You may connect to sharepoint list with Power Query and update the results with each opening of the Excel file and/or refresh by clicking on button.
Nov 23 2018 08:40 AM
thanks for that. are you able to point me in the right direction to doing that?
Nov 23 2018 09:15 AM
If you are not familiar with Power Query you may start from these support pages to check what's that.
https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de
And you may google for tons of samples and tutorials. But that takes time to learn...