SharePoint List Exported to Excel: Appended Local Columns

Tkr567

New Member
Joined
Feb 16, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi all

I have a SharePoint list exported to Excel – and I’ve appended some Excel columns with some comments which I have to keep local on an Excel file and can’t go into the original SP list.

This works ok except when the SP list columns in Excel are sorted and then refreshed – which leads to all the local comments swapping around to different rows.

I’ve looked this up and it seems that it’s a problem if the SP list is sorted before it’s refreshed

Does anyone have a solution for either:

(A) Preventing the local comments from swapping around upon refresh,

…or…

(B) Preventing the whole table from being sorted? This is to always keep the table sorted by ID. I’d need to retain the option to filter but we can live with just disabling the Sort option.
I had tried this through sheet protection (only stopping Sorting), but it prevented the whole Export from refreshing from the source data SP list - so would need an approach to either protecting the sheet which allows the SP-connected columns to refresh, or another way to prevent sorting.

Unfortunately, just 'don't sort the table' isn't going to work as I'm ultimately not going to own this table in the end!

For reference, this is how external data properties is set up:
1656938575401.png


Any solutions are appreciated!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top