Make a IF formula return a REAL blank cell if false

Hi All

I have a sheet with many columns, in one of those columns I need to use two formula's to generate a complete result.

The first formula I need to run in the column is: =IF((INDIRECT("RC[+1]",FALSE))=0,INDIRECT("RC[-1]",FALSE),"")

As you can see this tests the cell to the right, if there is a zero there then it returns the result from the column to the left of the original column. I am using the INDIRECT functions since I use the Edit->Replace function to affect the whole column.

For the false results I want to run the second formula which is: =VLOOKUP((LEFT(INDIRECT("RC[+1]",FALSE),2)),Sheet3!$A$1:$B$51,2,FALSE)

As you can see, this formula takes the first two digits of the data of the cell to the right, then looks for this on a table in sheet 3. Which returns a result.

That's what I want to do. However my problem lies in the edit->replace commands.

If I run the first formula via edit->replace all blank cells. It of course puts that formula in all the cells. However it then makes it impossible (as far as I know as this is my question) to run the second formula finding only those who returns a false on the first.

Even if I make the first formula return a "0" when false, I can't Find that "0" since the find all command finds the formula in the cell, not the actual value showing.





So I guess in conclusion. How can I make Find All find the actual value rather than the formula of a cell? If I could do that then I can get it to work.

Thanks in advance.

God haha. Sorry again I figured it out.

If I make it all part of a recorded marco, and copy the column, paste as special -> values between each formula it works fine.

Sorry for wasting your time lol.

---------- Post added at 02:59 PM ---------- Previous post was at 02:58 PM ----------

Originally Posted by vlady

hello
in Find/replace there's the "option" button there you'll find a "Look-in" option..(values,formula,comment)

And thats even better again. Cheers.