Returning a List of Formulas Based on a Specific #Excel Function by David Hager

I recently published an article which showed how to document the existing formulas in a range.

https://dhexcel1.wordpress.com/2017/08/17/documenting-formulas-in-an-excel-range-using-the-formulatext-isformula-and-textjoin-functions-by-david-hager/

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”.

FormulaTextByFunction1

In another example, the cells containing the FIND function are returned.

FormulaTextByFunction2

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.

FormulaTextByFunction

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s