sheet not calculating a "=index" formula that referes to a cell that has been changed by find and replace

I have a fairly large spreadsheet wherein a column of cells refers to an adjacent cell to lookup.
=INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))
I then noticed that one particular value in the "Lookup_value" cells needed to be modified so I ran a Find and Replace on the column to change a text value of "00000142" to "142". After that the formula that refers to the modified cell gives a result of "#N/A" and only if I "edit" the referenced cell (not changing anything but just using F2 to access the cell contents and then pressing enter. This would be ok if there were just a few, but there are over 1000 cells that need to be recalculated. I tried to "copy special" the entire column over itself using different options none of which made any difference. Of course, I also tried just recalculating the sheet and I exited/entered Excel all to no avail. Is there anyway to get these cells to be correctly referenced?
BobArnettAsked:
Who is Participating?
 
BobArnettConnect With a Mentor Author Commented:
Nope, didn't do it. It's odd because it's not the formula but the referenced cell that is the problem. Not doing anything to the formula but just using <F2><enter> on the referenced cell forces the formula to work.
Actually, however, what you said gave me an idea.
In a column three to the right of the problem text cell (say "H"), I entered the formula
=text(value(E5),"0##")
I then special copied (value only) that cell over the problem cell... and it worked.
I then did the whole column and ended up with no #N/A cells. Much quicker than one-at-a-time.
0
 
Wayne Taylor (webtubbs)Connect With a Mentor AstronautCommented:
Try deleting all but the top formula, then dragging the remaining formula down to fill the column.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You could select the column, then do a Find/Replace and replace all = signs with = signs. Make sure to find inside formulas, not values. That will recalculate all formulas.
0
 
BobArnettAuthor Commented:
Although Wayne's comment didn't do the trick, it did give me an idea on how to fix it. I figured I'd share the credit.
0
All Courses

From novice to tech pro — start learning today.