Forum Discussion

Conspicuus's avatar
Conspicuus
Copper Contributor
Mar 17, 2022

Online editing

I've created a worksheet that show data from a table using data validation and sort/filter. It was to be used by several colleagues one but when ever one is using it the information change for all. I...
excel
Excel for web
Conspicuus's avatar
Conspicuus
Copper Contributor
Mar 18, 2022

Hey NikolinoDE 

 

Thanks for your suggestions. 

I tried both solution. The sheet view was only related to the structure, not editing and the other would not work unless i converted all tables to range which was greyed out. The funny thing is that while I tested the sheet and had others test it as well the problem was not there so I have a feeling this is an upgrade from Microsoft (could be wrong) 

 

Best regards

Kim

 

 

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Mar 18, 2022
Thanks for the feedback

I am glad that your problem no longer exists.
It's a shame that it can't be tracked, but as Murphy's Law said...what works, works! :))

I wish you continued success with Excel.
  • Conspicuus's avatar
    Conspicuus
    Copper Contributor
    Mar 18, 2022

    NikolinoDE 

     

    I must have written it wrong. None of the solutions worked 😞 

    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Mar 19, 2022

      Conspicuus 

      If one person sets a filter, this filter is set for all other people at the same time.

      There is no really fix for it as far as I know in Excel for Web. As far as I know, excel for web does not have the commands "Shared workbook (legacy)" etc. to be displayed; nor in the "All Commands" list. In addition, if you have several features in your Excel spreadsheet that are not open according to "Share workbook", this option is not supported.

       

      A suggested solution would be to clear the filters before saving the file. The easiest way to do this is with VBA:

      In the ThisWorkbook code module:

       

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  ActiveSheet.ShowAllData

end sub

       

      And save the file as XLSM.

      Of course, all users must activate the macros (at least for this file).

      However, this approach leads to another side effect

      If you work in the cloud and "automatic saving" is activated, the filters are automatically reset, since the macro naturally takes effect every time you save.

      As a result, the other users are not annoyed by a jumping table but you are annoyed yourself.

       

      Another approach would be for users to disable auto-save.

      This in turn creates the problem that people can't see each other's changes in real time.

       

      You basically have the option of either working together and everyone sees the same thing or not working together, but it can be really impractical in some situations.

       

      At the end I would like to add that excel for web is rapidly getting more and more functional options. What is not available today can be taken for granted tomorrow, that is also the strength of Excel compared to other calculation programs. Perhaps someone has already found another solution, everything is possible in excel :).

       

      Thank you for your understanding and patience

       

      NikolinoDE

      I know I don't know anything (Socrates)

Resources