Forum home » Delegate support and help forum » Microsoft Excel Training and help » #REF! Error
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
#REF! Error
Resolved · Urgent Priority · Version 2003
Reply Mon 16 Feb 2009, 12:25Delegate Vinay said...
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.
For upcoming training course dates see: Pricing & availability
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
Reply Mon 16 Feb 2009, 15:26Delegate Vinay said...
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
Reply Mon 16 Feb 2009, 19:19Delegate Vinay said...
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
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
Thu 26 Mar 2009: Automatically marked as resolved.
|