July 17, 2018
I have an excel workbook on Sharepoint that multiple users can edit. From this workbook I use power query to extract data, this data is used in a self-referencing query to give me an also editable table that retains changes
My issue is when a user adds a new row they have to give it the next index number in the sequence, if another colleague is adding at the same time then the number could be duplicated. I have tried using formulas to create the index number but that causes its own issues during recalculation, ROW() changes if sorted, cell above +1 the same.
Both methods cuase problems with power query, I'd use vba to add a fixed value but vba won't work with Sharepoint.
Does anyone have any suggestions?