Remove all #Ref! Errors with cell references

Hi Experts

How would you remove the following cell #Ref! Error in a formula
Leaving just the active cell reference..

Assume:-

=sum(#Ref!AM123+#Ref!Al175+'Pro H!Al175)

Leaving:-
=sum('Pro H!Al175)
route217Asked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
Highlight all cells click [ctrl]+[F] find
Paste or tye the following into the find box: #REF!*+
Tab to replace type into replace with box +
Click replace all.

if you then have errors repeat with
[ctrl]+[F] find
#REF!*)
replace with box )

This will do it.
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You have to delete it manually.

It might be possible to write VBA code but that will have to be very specific for a particular scenario. To be able to write a general function to take care of all scenarios could be very extensive.
0
 
flaphead_comConnect With a Mentor Commented:
concur with ssaqibh, it looks like you removed or replaced a sheet with that name.

You can fix one of the cells and then use the the dot in the bottom right hand corner of the selected cell to copy the contents down the column
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
route217Author Commented:
Thanks for feedback - Experts
0
 
redmondbCommented:
Hi, route217.

The scale of this job is very dependent on the actual formulas. Please post a (redacted!) copy of the file - just be sure that it includes an example of each formula.

Thanks,
Brian.
0
 
Saqib Husain, SyedEngineerCommented:
I think that for the present scenario, where there is always the '+' sign at the end of the error term The_Barman's solution is the most suitable.

For any variation you would need VBA.
0
 
flaphead_comCommented:
good call The_Barman that is wicked, never thought of that

But there is a problem.  The Sheet Name is Pro H.  you cant use find and replace with the space between the Pro and the H.  Suggest you change the sheet name to Pro_H

Also make sure the sheet name you replace #REF! with has the ! at the end, so Pro_H!
0
 
SteveConnect With a Mentor Commented:
There is not a problem with the sheetname as it is not being replaced.
Only the #REF!*+ is being replaced with +
So there is no issue there.

Only issues are the double + which can be found and replaced or ignored.
0
 
flaphead_comCommented:
if you replace the #REF! in a cell reference, my version of excel validates the formula and so fails with the space in the sheet name
0
 
SteveCommented:
if you replace #REF!*+ with + this works, spaces or not.

the request is to remove the #REFS completely including adddress, not just the word #REF
0
 
route217Author Commented:
Hi Barman

True the #Ref!* but this deletes everything after the #Ref! So it's a no going

But worth a try
0
 
SteveCommented:
you MUST have the '+' after the '*' and replace with +

or it will not work...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.