ref error

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » #REF! Error

#REF! Error

resolvedResolved · Urgent Priority · Version 2003

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

Thu 26 Mar 2009: Automatically marked as resolved.


 

Excel tip:

Display developer tab - Excel 2010

a. In Excel, click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the box next to Developer in the list of tabs on the right hand side of the dialog box. When ticked the Developer tab will be visible.
e. Click OK to apply your changes

View all Excel hints and tips


Server loaded in 0.07 secs.