-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Clear cells (not delete) for those outcome of formula = REF accross worksheet
- Thread starter Fanny18
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,387
- Office Version
- 365
- Platform
- Windows
A better approach may be to update those formulas that are returning those errors.
Then you would never need to worry about it.
So if your original formula was "formula", here is how to rewrite the formula so that it will return a blank instead of REF errors.
=IFERROR(formula,"")
Just substitute your actual formula for "formula".
It might be a little bit of work to update the formulas across your worksheets, but it is just a one-time fix.
Once you update them, you should never have this problem again.
Then you would never need to worry about it.
So if your original formula was "formula", here is how to rewrite the formula so that it will return a blank instead of REF errors.
=IFERROR(formula,"")
Just substitute your actual formula for "formula".
It might be a little bit of work to update the formulas across your worksheets, but it is just a one-time fix.
Once you update them, you should never have this problem again.
Upvote
0
I have multiple worksheets and each worksheet have about 20 formulas. Any better and quick way to do so?
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,387
- Office Version
- 365
- Platform
- Windows
Isn't just a matter of updating the first formula, and then copying the formula down or across (so you don't actually need to update each and every formula manually)?
The advantage to using this approach is once you do this once, it should be fixed, and you don't need to go through and run some code or update things manually every time.
Preventing an error is almost always preferrable to trying to fix it afterwards.
You may even be able to use VBA code to make these "one-time" fixes for you automatically, if there is any pattern/method to these formulas (where they occur on the sheets, what the formulas are).
If you provide the specific details, we can probably help you with that.
The advantage to using this approach is once you do this once, it should be fixed, and you don't need to go through and run some code or update things manually every time.
Preventing an error is almost always preferrable to trying to fix it afterwards.
You may even be able to use VBA code to make these "one-time" fixes for you automatically, if there is any pattern/method to these formulas (where they occur on the sheets, what the formulas are).
If you provide the specific details, we can probably help you with that.
Upvote
0
Similar threads
- Question
