Posts Tagged ‘Custom List’

Sharepoint List and Excel

March 27, 2014 1 comment

SharePoint List is one of the most powerful feature in SharePoint that can be used for any purposes. However I found it is difficult to link it with Excel with multiple sheet and source. For instance, we had two Sharepoint List and we would like to link it on a two sheet on a single Excel File?

If you look at data tab on Excel, you may not find SharePoint List menu but you can find it easily on Access. In Acces Database we could link two or more SharePoint List that can synchronize each other. How about Excel ? Here are the clue or workaround

– in Access, link two or more SharePoint List from import data tab
– create query with the same field and structure of the table ( SharePoint List ). The point is you make a local copy of the table
– open Excel and go to data tab, import from Access Database file on point 1 and 2 and link them with the queries
– your Excel file is now connected with SharePoint List with Access File as the connector. You do not need to open the Access File, whenever you open the Excel File it will be synchronized with the data from SharePoint list. Make sure you check refresh option when opening the file.

Nice trick