I recently published an article which showed how to document the existing formulas in a range.
You might want to go back and read that article, since the formula demonstrated there was the starting point of the formula presented here.
I wanted to modify that formula so that it would return formulas from a range that contained a specific Excel function. The desired formula for that purpose is shown below.
=TEXTJOIN(CHAR(10),TRUE,IF(NOT(ISERROR(FIND(Function&”(“,FORMULATEXT(fRange)))),ADDRESS(ROW(fRange),COLUMN(fRange))&”:”&FORMULATEXT(fRange),””))
where fRange is the defined range E2:F14
and
where Function is a defined range (in this case, K1).
One point of interest is that this formula does not require the use of ISFORMULA (as in the previous article) to validate whether a cell contains a formula since, for example, the string “SUM(“ would not be found in a cell not containing a formula.
So, this formula “looks” in each cell in fRange and if the function name in K1 is found in a cell, a string with the cell address and the formula is added to an array, which is processed and formatted by the TEXTJOIN function in cell K2 to afford the desired list. In the following figure, the result can be seen for finding formulas containing “SUM”.
In another example, the cells containing the FIND function are returned.
Note that even though some of the formulas return errors, this demonstration still shows the actual formula from each cell meeting the desired criteria.
Finally, if the value for the cell is desired, an expression for that can be added to the main formula. I do not have any plans to do that.
I hope that you find this technique useful.
You can download the example file here.
Excel For You 