Embed/reference formula text inside another formula

[anblan25]

Hi, I'm trying to embed formulaB8 into formulaB9, resulting in valueA11.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]formulaB8 :[/TD]
[TD]SUM(A2:A4)[/TD]
[/TR]
[TR]
[TD]formulaB9 :[/TD]
[TD]=SUM(formulaB8,2)[/TD]
[/TR]
[TR]
[TD]valueA11 :[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to do this without actually pasting the formulaB8 text into formulaB9.

Since I have one large formula that is re-used many many times inside other formulas, I'd like to be able to keep that formula text in one cell and embed the entirety of its contents inside the text of other formulas using some sort of reference.

Below is a simplified example of everything I've tried in an effort to realize this functionality.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]value[/TD]
[TD]formula
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1[/TD]
[TD]=1[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2[/TD]
[TD]=2[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3[/TD]
[TD]=3[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]6[/TD]
[TD]=SUM(A2:A4)[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]SUM(A2:A4)[/TD]
[TD]SUM(A2:A4)[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]=SUM(formulaB8,2)[/TD]
[TD]'=SUM(formulaB8,2)[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]8[/TD]
[TD]=8[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]SUM(A2:A4)[/TD]
[TD]=CELL("contents",A8)[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]#VALUE![/TD]
[TD]=SUM(CELL("contents",A8),2)[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]#VALUE![/TD]
[TD]=SUM(CELL("contents",INDIRECT("A8")),2)[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]#REF![/TD]
[TD]=INDIRECT(A8)[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]SUM(A2:A4)[/TD]
[TD]=INDIRECT("A8")[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]2[/TD]
[TD]=SUM(INDIRECT("A8"),2)[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]SUM(A2:A4)[/TD]
[TD]=INDIRECT(CELL("address",A8))[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]2[/TD]
[TD]=SUM(INDIRECT(CELL("address",A8)),2)[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]=SUM(A2:A4)[/TD]
[TD]="=" & INDIRECT(CELL("address",A8))[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]#REF![/TD]
[TD]=INDIRECT(CELL("contents",A8))[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]#REF![/TD]
[TD]=INDIRECT("" & CELL("contents",A8))[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]#REF![/TD]
[TD]=INDIRECT(TEXT(CELL("contents",A8),"General"))[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]#VALUE![/TD]
[TD]=SUM(name1,2)[/TD]
[/TR]
</tbody>[/TABLE]

note : name1 was created in Name Manager and set to a value of "SUM(A2:A4)" (without the quotes)

The furthest I feel like I've progressed is valueA22, but it seems like it's treating the referenced A8 as a text with a numerical value of 0, and I'd like the A8 text evaluated within the context of formulaB22.

I've searched online and have been spinning my wheels tinkering with it to no avail.

Any assistance would be greatly appreciated. Thanks.

 

[James006]

Hello,

My understanding is that you need to use the Evaluate() function (Excel v4.0 macro function)

So, you can use the Name Manager to create the your own name such as : name1

In terns of Refers To, you should have : =EVALUATE(Sheet1!$B$8)

Then, in your selected cell, just type in : =name1

Hope this will help