Updating SharePoint List from Excel

MedhAmrita1525 

Hi

The solution I've been using for years is the following :

• from the SharePoint List, define a specific view containing the fields you need and also the "ID" field (I generally call this view "ExportToExcel"). Click "Export to Excel" and accept all pop-ups.
• This creates an Excel table. Give it a name, of course (I prefix such tables with "sp" so let's name it spMyList)
• Back to the SharePoint list, build the urls for "New item" and "Edit item"
  • <base url>/NewForm.aspx
  • <base url >/EditForm.aspx?ID=
• Copy these urls as text in 2 cells, in a separate sheet in the Excel file (prefix them with a quote)
• Give names to these cells : urlNew and urlEdit
• In the table spMyList, insert a column on the left, so that this will be the first column of the table. Insert it IN the table, not before the table.
• Add a formula in the column: =HYPERLINK(urlEdit&[@ID],"Edit")
• This will create an hyperlink on each row, allowing to open in edit mode each item. Once an item has been modified and saved, go back to Excel and refresh spMyList
• Add a row above the spMyList table. In cell A1, enter this formula: =HYPERLINK(urlNew;"New"). This hyperlink will allow you to cretae a new item in the list. Just refresh Excel when it's done to see it in the list. (You can also create a button associated with the url to do that).
• Freeze rows 1 and 2.

There are many other tips to facilitate this way of working. I must make a video one day to share that!