Vinay has attended:
Access Intermediate course
Access Advanced course
Excel Intermediate course
Excel Advanced course
#REF! Error
I am using a formula (=GETPIVOTDATA("Total2",Pivot!$A$3,"Product Code",A5,"Currency",$CT$4,"Monthly Period",$CM$2,"Weekly Period",$CS$3)) from a pivot table but i am currently getting #REF! Error messages, but the formula is working.
Is there any way that instead of displaying #REF! Error message that i can disply it as 0.00.
Please can you help.
RE: #REF! Error
Hi Vinay
Thank you for your question.
I think if you nest your GETPIVOTDATA formula inside an IF function you can achieve this, but I think you will need to put this formula into another cell in order to avoid creating a circular reference. So I suggest you do this and then hide the cell that displays the REF error.
Assuming the formula you have is say in cell A1, the nested function would look as follows:
=IF(ISERROR(A1),0,GETPIVOTDATA("Total2",Pivot!$A$3,"Product Code",A5,"Currency",$CT$4,"Monthly Period",$CM$2,"Weekly Period",$CS$3))
I hope this helps.
Amanda
RE: #REF! Error
It Still Does not seem to be working. because if i use that formular then the results doesnt seem to be coming up .
RE: #REF! Error
Hi Vinay
Did you put the formula into a different cell from the GETPIVOTDATA formula you currently have in the spreadsheet?
thanks
Amanda
RE: #REF! Error
I put the formular in another cell and changed cell reference to what it should be.
RE: #REF! Error
Hi Vinay
If possible, you could email it through to me if you like so I can have a look - it's difficult to be able to solve problems with formulas without seeing the spreadsheet.
I won't be able to have a look at it until tomorrow, but if you would like to send it through please email amanda@stl-training.co.uk
thanks
Amanda
RE: #REF! Error
Hello,
This question has now been left unattended for a while.
As we are waiting for your response for further information to help resolve your issue, we will be automatically marking this question as resolved in the next 5 days unless you post a follow-up within that time.
Have you sent the e-mail to Amanda?
Thank you.
Rich
