Shared workbook opening at the same cell every time instead of last edited cell
The file saves the changes made but continues to open to the same cell (frustratingly near the top of the page) whenever it’s opened.
It is a shared workbook and needs to remain shared, but i did notice that when unshared and then shared it again, it opens at that new point. but i really don’t want to do this every time, as it deletes the edit history.
Is there some box i have unchecked? or some way i can “share” the document when it’s saved so that it opens at the most recent edit?
Posted Jan 28, 2020, 5:46 PM UTC
311
I just confirmed this is a reproducible issue at least to me. Excel 2016, not o365.
Created brand new blank file, saved to a network drive. entered "x" in A1, B1 and C1, left focus on cell D1. Turned on Share Workbook, saved and closed.
Opened, focus still on D1. Moved focus to B1, saved, closed and reopened, focus back on D1. Repeated multiple times.
Then tried deleting and entering x in B1 again, hit tab when done, focus now on C1, save close reopen focus back to D1.
My guess, the default cell focus is set to whatever cell had focus prior to applying sharing and the shared workbook feature locks this in place. Unlike the behavior of a non-shared workbook saving the focus of the cell at each save.
Have not figured out the why yet but it would seem a quick albeit imperfect solution is un-share, set desired cell focus, apply sharing (or knowing this to set focus prior to the first share as desired).
Intrigued, gonna dig into this and see if this is expected behavior, a bug or what.
Found the culprit...personal views. When the workbook is shared each user should end up having their own personal view automatically set/saved and applied to the workbook.
This means that if I usually work on Sheet1 and you usually work on sheet2, when I open it I am on sheet 1, when you open it your on Sheet2 and stuff like freeze panes, etc is specific to your view.
You can see these personal views from View | Custom Views. If you delete the personal view, move focus and save/close/reopen then the change will be reflected. Or alternatively you can create a new personal view, change focus, save close reopen and then open the personal views and hit show on each one to see that it does keep this focus saved on a per personal view basis.
This is where things get weird. The first time I deleted my personal view, changed focus and closed saved reopened, it kept my change but generated a new personal view so when I changed it again, closed saved opened it went back to the last change not my latest focused cell.
However when I tried it again, my personal view was regenerated but now each time I change focus and close save reopen without messing with the personal view it now does save the changed focus...
So I am going to chalk this up to a combination of the old, dated, always problematic shared workbook feature and personal views. On one hand the personal view saving the focus on a per view basis makes sense when multiple people may be using the file...on the other hand it does not seem to gracefully realize a specific users change to focus for their own personal view.
I see... Thank you a ton for this, i’ll continue to fiddle around with it.
Yea so the way I see it, the concept makes sense right.
Like how bad would it suck if you and I work in the same shared file, you save with focus on Sheet1 A1 and then I save with focus on Sheet10000 ZZZ99999 and then when you open your stuck where my focus was, right?
So the premise makes sense, you have your "sandbox" and I have mine. Yours has your cell focus, active sheet, view (normal, page layout, etc), etc. and mine has mine.
Here is where I think MS drops the ball. In a perfect world, when I am logged in as the same user on the same computer and jump into the file, Excel should know its me, load this personal view AND also keep ALL of the saved changes to it, including cell focus etc.
Problem is, that does not seem to be the case. Instead it works in some pseudo custom view thats not saved and instead loads your personal view the next time you open with old "settings", at least re: focus.
The work around seems to be deleting your personal view, setting desired focus and then save close reopen...change focus again save close reopen and see if its resolved.
PS: maybe coincidence...between deleting my personal view and the problem remaining long term and me deleting the personal view and the problem going away forever I did create a new personal view manually with a different focus then my auto generated personal view, saved closed reopened and then deleted both my auto and manually generated personal views.
Its possible the problem is version specific to Excel and/or OS, maybe even specific patch/update versions. IE 2016 may have the issue on Win7 but 2013 might be fine on Win10...who knows.
Hope this helps, was interesting to track down the culprit. Couldn't find any official documentation or even 3rd party about this issue, specifically shared workbook and focus cell. Didn't look more into personal views + shared work book beyond my own findings tho.
Yeah, they definitely had good intentions!
Super interesting stuff
Making new focuses, deleting current ones, etc hasn’t solved it yet for me.
Once i save, like you said, it creates a new personal view and it seems to stay locked into that view until i delete it and re-save.
Solution Verified
You have awarded 1 point to Zer0CoolXI
^I ^am ^a ^bot, ^please ^contact ^the ^mods ^for ^any ^questions.
Yup, that’s exactly what’s going on. Thanks for taking interest.
Interestingly, a file from last years data, which is still shared, is working just fine and opening to the most recently focused cell. looking through the setting to see if anything is different now
[deleted]