Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .