Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Filter Function #DIV/0!

Copper Contributor

I am creating a dynamic list that returns values in a column based on another column. The data is being pulled from another worksheet with cell references to a pivot table (ex: =sheet2!A1); however, when I filter the pivot using a slicer, the filter function stops working and returns #DIV/0!. The information is still being pulled into the cells that the filter function is using so I am unsure of what is causing this issue.

2 Replies

@Wallsda Difficult to visualise, but when you refer to a cell inside a pivot table like you say, that particular cell might change from a number to nothing when you apply a slicer. This cell would then become a zero and may lead to a #DIV/0 error, depending on how you use it in the formula.

 

Can you share a workbook or a screenshot showing "before" and "after"? That would help a lot.

 

Thank you for the interest in helping me solve this issue. I got it resolved. The pivot table causing the issue was showing calculations as a percent of row total like I wanted, but when I referenced that data on a different sheet, it was pulling the error with it which caused the filter function to only return div/0. I just used an errorif function to filter out the errors which fixed the issue.