Mar 17 2022 07:26 AM
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. Is it possible to work online without showing changes to all?
Mar 18 2022 05:44 AM
Here some informations for Excel for web.
Have you ever collaborated with someone else in a worksheet, looking at a large data set, and suddenly the table shrinks and you’re unable to finish your work? It's pretty disruptive isn't it?
Additional info for 365 Abo:
What happened to shared workbooks?
Hope I was able to help you with this info.
Thank you for your understanding and patience
I know I don't know anything (Socrates)
Mar 18 2022 06:52 AM
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
Mar 18 2022 08:16 AM
Mar 18 2022 11:55 AM
Mar 19 2022 02:58 AM
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
I know I don't know anything (Socrates)