Changing #REF! from Pivot Tables to 0%

Wimbers

New Member
Joined
Jul 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you so much for your reply. One quick question

What characters are || ?

Much appreciated
 

Wimbers

New Member
Joined
Jul 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
All good now.

Tank you very very much for your help
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,606
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Wimbers

New Member
Joined
Jul 4, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 :):):):):):)
 

Forum statistics

Threads
1,141,815
Messages
5,708,740
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top