Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I had a colleague ask me yesterday if it was possible to set an auto-filter to display columns based on the text of formulas in the cells instead of their calculated results.

For example: if I had formulas =D3*3, =D3*4 and =D3*5, I'd want to filter based on the *3, *4 and *5 parts of the formulas.

Is this possible? From the choices in the UI, it seems that my colleague may need to use VBA to solve the problem.

share|improve this question

1 Answer 1

to my knowledge there is no built in function that will return the formula contained in a cell.

The simplest solution is to create a small helper function in vba

Function CellFormula(r As Range) As Variant
    CellFormula = r.Cells.Formula
End Function

and put this in a column next to the data, and include that column in the filter range. Then the formula strings are available for filtering

Note: if you have array formulas in your sheet and want to identify that in the result, the helper function will need some modification

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.