I cannot change a drop down option if I have sorted the data on the spreadsheet

Question

We have all of our workload logged onto a tracker via excel. Due to the volume of work, we have a team of around 8 people who use this tracker every day and we all work in "Temporary View" so we can filter the tracker based on the tasks we have been assigned.

Several months ago, we stopped being able to change the "status" of our work when we had filtered the tracker. I have noticed the issue is when we sort the data into date order (we select "Sort Oldest to Newest") - this is really frustrating because the entire team have to work in this manner but once the task is complete, we cannot change the status to "Completed" as the below error message pops up.

Does anybody have any idea what I can do to resolve this? The drop down filters are set up under "Data Validation" and we have the "In-cell drop-down" box ticked; we haven't got anything grouped and the advanced options seem to be in order.

I'm not sure whether Microsoft have changed the abilities or whether there is something I can do to fix this... Any advice would be appreciated!

Answer 1

View>Sheet View>Exit sheet view or choose "Default view". Then try again.

Answer 2

Hi there,

That doesn't help at all because we have over 1,000 lines of data. When you exit the sheet view, the data resets to the default view and you then have to search for the line you were working on. We need to be able to change the drop down option when we are in the Temporary View.

Many thanks.

Answer 3

It is design limitation. The action only can be done on default View.

You may send feedback to Microsoft. Hope Microsoft will make this action available in Temporary View at next version.

Excel · Community (microsoft.com)