Need to remove #REF from total

[Dan15]

Here is the situation:

I am totaling the sums from several different sections of worksheet 1 onto worksheet 2. Several of the sums on worksheet 1 will be deleted, causing the error #ref on worksheet 2. How do I total the sums, exluding the #ref

Thanks-

-Dan

 

[Zathras]

If you are careful to delete the cells/rows that contain the references (but leave the sheet) you can keep up to date with Edit/Replace.

Say you start with

B2: =Sheet2!A1+Sheet3!A1+A1

Then delete rows/cells from Sheet3, you get this:

B2: =Sheet2!A1+Sheet3!#REF!+A1

not only in B2, but anywhere a cell is referenced from sheet3. Now you can replace

   +Sheet3!#REF!

with nothing and get

B2: =Sheet2!A1+A1

You also need to handle other variations like

   =Sheet3!#REF!

and perhaps others, depending on what formulas you have.
Now you can work with the next sheet. If you don't work one sheet at a time, the replacement formulas will be erroneous and the revision will be rejected.
After doing the above, you can delete the sheets, if that is what you want.

Hope this helps.

 

[PepeLeMokko]

Don't know if this helps.
To avoid getting the #ref error, select the range with the formula results, put your cursor near to the upper limit of the selection ( it will become an arrow), and holding the right button of your mouse , drag your selection to a location ( can be the same from where you started the idea is that you move around a bit).
When you release the button a menu will pop up stating " Copy here as values only" ( among other things).
Choose this option, and you can delete any precedents if necessary.
If you record a small macro to do this, you are in business

 

[Dan15]

The solution needs to be a formula I do not have to modify - if I have the equation:

=Sheet2!A1+Sheet3!A!+A1

and it becomes

=Sheet2!A1+Sheet3!#REF!+A1

when a line is deleted on sheet 3, is there a way I can report any cell with a #REF as simply a 0? I was playing around with the =ERROR.TYPE function, and although it reports the #REF as a number, it reports the normal numbers back as #N/A, which is also unusable in formulas.

 

[PepeLeMokko]

Did you try using the iserror function?

 

[Dan15]

Coyoteooc -

Problem solved - I was not aware of the iserror function.

Thank you very much for your help

-Dan