How to "Remove" #REF!???

H

hce

Dear All

A very simple question which has puzzled me... I have a formula in
cell and sometimes it will return the value as #REF!... when the cell
mentioned in the formula has no data...

How can I make the formula such that if the value is #REF!, instead o
showing #REF!, it will show as 0...??? I tried using IF but it doesn'
seem to help...

Any suggestions???

Cheer
 
F

Frank Kabel

Hi
could you post your formula?

Normally you culd use something like
=IF(ISERROR(your_formula),"",your_formula)

or use something like
=IF(A1="","",your_formula)
 
G

Gord Dibben

You should not get #REF just because of no data in the cells.

You get #REF when cells referred to in formulas have been moved or deleted and
the formulas longer refer to valid cells.

e.g. Enter "cheers" in A1.

In B1 enter =A1 to get "cheers"

Now delete column A.

You will get #REF in A1.

From Help on #REF.................

Correct a #REF! error
Occurs when a cell reference is not valid.

Click the cell that displays the error, click the button that appears , and
then click Trace Error if it appears.
Review the possible causes and solutions.
Possible causes and solutions
Deleting cells referred to by other formulas, or pasting moved cells over
cells referred to by other formulas

Change the formulas, or restore the cells on the worksheet by clicking Undo
immediately after you delete or paste the cells.

Using a link to a program that is not running

Start the program.

Linking to a Dynamic Data Exchange (DDE) topic such as "system" that is not
available

Make sure that you're using the correct DDE topic.

Running a macro that enters a function that returns #REF!

Check the function to see if an argument refers to a cell or range of cells
that is not valid. For example, if the macro enters a function that refers to
a cell above the function, and the cell that contains the function is in row
1, the function will return #REF! because there are no cells above row 1.


Gord Dibben Excel MVP
 
Top