Changing #REF! from Pivot Tables to 0%

[Wimbers]

Hi,
Windows and Office 365. I am building an Excel dashboard based on around 200 pivot tables and slicers. The data from the pivot tables is put into other excel spreadsheets within the workbook. If there is no data from the pivot table it returns #REF! for each relevant cell.
Is there a way I can automate adding IFERROR to the individual GETPIVOTDATA links so it returns zero% for all the hundreds (or even thousands) of spreadsheet cells affected?
Or is there another way to change the #REF! to show zero%?

Thank you

 

[jasonb75]

Or is there another way to change the #REF! to show zero%?

If showing nothing in the cell is acceptable then you could use conditional formatting to match the font and fill colours so that the error can not be seen (not so practical if you have a technicolour dashboard).

Adding iferror to a range of formulas can be done quite easily with find and replace if the formulas are all single functions, if you have nested functions or a variety of different functions in the cells then you would need to use vba instead.

The find and replace method (for the GETPIVOTDATA function).

1. Select the data range with the errors.
2. Press Ctrl g
3. Click the 'Special' button, then choose Formulas and Errors from the options (uncheck the other boxes). Click OK
4. Press Ctrl h
5. In the 'Find What' box, enter =GET
6. In the 'Replace with' box, enter ||
7. Uncheck the 'Match case' and 'Match entire cell contents' boxes (click the Options>> button to show them if they are not visible).
8. Click 'Replace All'
9. Repeat steps 4 to 8 to replace ) with ),0%)
10. repeat again to replace || with =IFERROR(GET

It feels like a bit of a messy process, but for simple formulas I find it quicker and easier than the vba alternative.

 

[Wimbers]

Thank you so much for your reply. One quick question

What characters are || ?

Much appreciated

 

[Wimbers]

All good now.

Tank you very very much for your help

 

[jasonb75]

What characters are || ?

Rarely used in anything

If you try to edit the formula with the = symbol at the start then it will not work. Replacing the first part of the formula with || temporarily changes the formula to a text string so that it can be edited without problems. You can use whatever you like for that part, I use || as it is unlikely to be found anywhere else in the formulas, which means that there are less likely to be problems with the wrong part being replaced.

 

[Wimbers]

I have not stopped smiling as you have solved a frustrating problem and given me a great solution, thank you so much.

I have now converted it into a macro.

I am truly grateful for your assistance

 

[bjayani]

Hi! I joined to this community today. I'm having the same issue as to change the #REF! to show zero% in excel dash board I'm building. Can anyone help me.

Thank you