[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • Last Modified:

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)
0
route217
Asked:
route217
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
Saqib Husain, SyedEngineerCommented:
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_comCommented:
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
 
SteveCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
SteveCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now