Excel cell to populate from live sharepoint list

Copper Contributor

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

3 Replies

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.

thanks for that. are you able to point me in the right direction to doing that?

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

https://support.office.com/en-us/article/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-...

And you may google for tons of samples and tutorials. But that takes time to learn...