How do you refer to the text of a formula?

[kwkeller]

Hello list,
How would I refer to the text of a formula in another formula? For ex:

If I have the following formula

=SUBTOTAL(3,D2:D10) which equals 9

I would like to extract the range D2:D10 out and use it in another formula. However all that I'm geting is the result of the formula (9) not the "text" of the formula.

How do you do this?

 

[plettieri]

Is this what you mean...you can name the range and then use that range name in wherever you want on the spread sheet?

Insert | name | define ..gets you to name the range..


pll

 

[Joe Was]

Combine both text functions and value functions into a string. For Sum(B1:D1) use:

="SUM("&B1&"+"&C1&"+"&D1&")="&SUM(B1:D1)

This is what you will see in your cell:
SUM(10+10+10)=30
if B1,C1&D1 each = 10.

And

="SUM(B1="&B1&" + C1="&C1&" + D1="&D1&")="&SUM(B1:D1)

will give: SUM(B1=10 + C1=10 + D1=10)=30
in the cell containing the mixed text formula above.

Hope this helps. JSW

 

[Asala42]

If I have the following formula

=SUBTOTAL(3,D2:D10) which equals 9

I would like to extract the range D2:D10 out and use it in another formula. However all that I'm geting is the result of the formula (9) not the "text" of the formula.

Here 's a way if you're not opposed to a UDF approach. To Start, here's a little UDF that will translate a formula to a string of text:

Function FormulaText(x As Range) As String
     FormulaText = x.Formula
End Function

In another cell, type the formula:
=FormulaText(D12) (assuming your subtotal formula is in D12)
This should kick out the string: "=SUBTOTAL(3,D2:D10)".

Now you're free to wrap a Find/Mid Combo to pull the D2:D10 part of the string. example:

=MID(formulatext(D12),FIND(",",formulatext(D12))+1,6) will pull "D2:D10" as a string. You could then use INDIRECT with that string in other formulas.

Hope that helps somewhat