Excel Filter by Formula 1

[sxschech]

Can I filter by formula rather than values? I have a simple formula in Col K. Example: =T54
What I would like to do is perform a filter showing all the rows where the formula in Col K begins with a letter other than T, (Where Formula <> "=T*")?

 

[GlennUK]

You can view all formulas by changing the View options, menu command Tools/Options/View and tick the Formulas checkbox in the Window Options section ( shortcut to toggle Formulas is Ctrl-' ( that's Ctrl key and the left single quote that is to the left of the 1 key on a UK keyboard ).

To filter on formula you'd need an extra column to contain the text of the formula, and that would have to be generated by VBA, or a VBA User-defined Function, or a Defined Name macro formula. I'd use the Defined Name macro formula ... Insert/Name/Define, CellLeftFormula with a definition of =GET.CELL(6,cell_ref_cell_left_of_current_cell)
which will show the formula of the cell to the left of the cell where =CellLeftFormula is entered. Then you can filter on that column.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[sxschech]

Hi Glen,

I probably am misunderstanding a step or two...

Copied the formula
Clicked on Insert, Name, Define
Entered "CellLeftFormula" in "Names in WorkBook"
Pasted "=GET.CELL(6,cell_ref_cell_left_of_current_cell)" in "Refers to"
Clicked Add
Clicked OK
Inserted a Col after Col K
Pasted "=CellLeftFormula" in the new col
Also tried "=CellLeftFormula(K5)"

Both returned #Name?

 

[GlennUK]

You need to put the cell reference of the cell to the left of the active cell instead of cell_ref_cell_left_of_current_cell.

( that's the active cell when you do the menu command Insert, Name, Define, without dollar signs in there ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Hi,

Sort of a coded solution, here's a VBA function to us in a column that you would then filter on...

Function WhatFormula(r As Range)
    WhatFormula = r.Formula
End Function

Paste in a MODULE in your workbook or PERSONAL.XLS

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

 

[sxschech]

Thanks Skip. Works Great.