Excel - search formula string or filter by formula 1

[osx99]

I have a range with formula entered but users have overtyped some of the formulae with actual values

eg. I have a mix of
contents of cell A1 - "Y"
contents of cell A2 - =VLOOKUP FG101,$GM$98:$GN$135,2,FALSE) with result "N"

I now need to change the formula but retain the overtyped value. Is there a way to identify when a formula is used instead of the result?

Many thanks,
Os

 

[SkipVought]

Hi,

Tools > Options > View Tab: Window Options-- Check FORMULAS

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Same answer as Skip's - but the keyboard shortcut is [Ctrl]+[`] where [`] is the key at the top left of the keyboard, to the left of the [1]. The shifted value of this key is tilde ([~]).

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[osx99]

Thanks for the advice but I have found a nicer solution for me

Create a new module and insert the following code

Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

then within excel use the function

=IsFormula(FU100)

 

[SkipVought]

If you want a VBA code solution, then ask in Forum707. This forum focuses on solutions that can be achieved by native Office functionality.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

I have one more option for you.

You can: Select the range and CTRL+G, Special, and then tick the box "Formulas". It will highlight cells which contain formulas, you can choose the colour for those sells (blue) and then work with them.

Yuri