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?