Filtering deletes formulas within the cells and parent rows

Wanda T

I've been struggling all day with a complex sheet, discovering multiple times throughout the day that formula's appear to have been deleted.

Before Filtering- every cell had a value, either 0.000 or greater; parent rows were totalling correctly.

Action:

Filter column'Q1 Total' to show only those cells that had a value (as a result of a formula in the cell (=[Q1 Lic Rev]70 + [Q1 NRE]70 + [Q1 ReSW]70 + [Q1 Mtce]70)); when I did not get the desired result I 'Removed All Filters'.

Result:

• formulas are deleted (the cell is now blank) when; cells had a Q1 value; as well as the SUM(Children()) formulas for the parent cells (where a child had a Q1 value)

It appears formula's and data elsewhere in the sheet may have been deleted as well, however will not know for certain until I rebuild the missing data

• formulas are intact: for cells that were $0 (as a result of the formula in that cell) the formula is intact.

This has resulted in hours and hours of rework today and a company wide blast to NOT USE FILTERING

Please advise if I need to open a trouble ticket.

Tim Meeks

W,

So sorry to hear. That clearly stinks.Just to be safe, I would submit the issue to Support. They may be able to get a back up of the sheet. I've not used Filters where I also had formulas so I don't know the result.

Two suggesions:

1) if you don't have a backup running on that sheet, I would suggest doing so. Just search for "Backup a sheet" in Help.

2) To pull the data you need, I would suggest using a Report instead of Filtering. That way your formulas will stay intact.

Hope your week goes better!

Tim

Travis

Hi W - this is certainly strange. Filters do not have any effect on data located in any cell. Filters are only able to hide or show rows and cannot change or remove any data from your sheet.

Email the Support team ([email protected]) and they can look into the sheet history to troubleshoot what may have caused this to happen.

I am sorry this happened! The Support team is great at what they do and can help get to the bottom of this.

Wanda T

Yes very impressed with support.

Yes - the top line of the sheet now says in 28pt font "DO NOT Filter, call me if you need a report!"

I sent an email last night to support, she is waiting for me to share the sheet (unfortunately the data is highly confidential financial type data so I will have to create a duplicate and fudge all the information (leaving formulas intact) before I can share).

I have scheduled an excel export once a day so that I have the information at least. It was really and truly bizarre, I wouldn't have believed it if I hadn't been watching the screen and very aware of the data (it was pretty obvious though as it had deleted the sum(children()) in the parent row in addition to the formula in the cells so my totals were gone). I also noticed after posting the query here that in the bottom section of the sheet parent rows had mixed formatting (ie. a row that I had shaded all light brown; now had some cells within that row were reverted to No fill). Sorry can't explain it. I wonder if there is a limit on how many formulas it can handle - it is a pretty formula 'rich' sheet with calculations (IF, AND, ISNUMBER, SUM mostly)

Travis

Thanks Wanda. It will be helpful to the Support Rep if you could also provide the name of the sheet and the approximate time/date this issue occured. I will continue to monitor your Support ticket to ensure the issue gets resolved.

Wanda T

Thanks Travis, I have shared a replica of the sheet with support although I have been unable to reproduce within the replica. Perhaps it was a fluke due to the heavy formula's and that others were viewing the sheet at the time, however I am not going to be filtering that particular sheet again. Fortunately, that means folks are getting familiar with Reporting