Forum Discussion

Colleen's avatar
Colleen
Helper I
7 years ago

Remove Error not working

I'm extracting data from excel where there are #REF errors in the spreadsheet. For some reason remove errors will not remove them. So far I have tried:

 

  1. Remove errors function from remove rows
  2. Delete row (this will not work as it cannot delete an error)
  3. Replace errors
  4. Removed alternate rows (targetting the single error row)
  5. Refreshing data source

Even with multiple attempts on each of these steps, the result is either nothing (steps 1,3,4,5) or they won't work as they throw an error themselves (2).

 

Here is a screenshot to demonstrate my problem. The error is on the last row, and the #REF error is from the data source where a user has attempted to divide a cell by another #REF. I cannot change the data source.

Error cannot be removed

How do I overcome this problem?

Need Help

4 Replies

    • Colleen's avatar
      Colleen
      Helper I
      7 years ago

      Hi Anonymous ,

       

      I already saw that link and followed the solution which was to replace rows. As shown above this doesnt work.

       

      Kind regards,

      Colleen

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hey Colleen,

            Follow all the steps in the link. Replace with value  "null". Then right click on the column header and select "Remove Empty"...worked for me 

        Regards,

        L

    • JJJJJJJJ's avatar
      JJJJJJJJ
      Frequent Visitor
      3 years ago

      Please did you ever find a solution to this? I'm absolutley stumped in a similar situation, years later. The only thing I seem able to do is to fix the #REF! error in my source Excel file. This is a wierd scenario where it's essentially a step-level error, but it should be a cell-level error. Because of this, replace / remove errors is not working as that only picks up cell-level errors.