How do you refer to the text of a formula?

kwkeller

New Member
Joined
Jun 26, 2002
Messages
11
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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:

Code:
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
 

Forum statistics

Threads
1,144,765
Messages
5,726,164
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top