Convert string into formula

[Dario Jardim Junior]

really needing this...

let's say A1 = "SUM"
B1 = "("
C1 = ")"
D1 = "A3:C8"

How can I put everything together and get the FORMULA =SUM(A3:C8) and its
result INSTEAD of the STRING "=SUM(A3:C8)"?
it's drivinhg me mad....I've tried CONCATENATE, &,... and all I get is the
string...

Thanks a lot


Dario - Brazil

 

[Philip Reece-Heal]

Dario

Maybe better ways but this works:
Using your example, in E1 type the formula =CONCATENATE("=",A1,B1,D1,C1).
Then, with cell E1 selected, do Edit Copy then Edit PasteSpecial and choose
Values. Then hit F2 and then Return.

Regards
Philip

 

[macropod]

Hi,

Will =SUM(INDIRECT(D1)) do what you're after?

If not you'll need to do something like ="="&A1&B1&D1&C1 to concatenate the
string, then select the result, copy it and do Edit|Paste Special|Values,
followed by selecting the result, putting the cursor just before the '='
sign and pressing the backspace key. Alternatively, you could run the
following macro after selecting the cell where you want your formula to
appear:

Sub FormulaMacro()
ActiveCell.Formula = "=" & Range("A1") & Range("B1") & Range("D1") &
Range("C1")
End Sub

Cheers

 

[Earl Kiosterud]

Dario,

Concatenating that stuff will yield a string (text) that looks like a
formula. Excel knows it's text, and simply displays it. It has no more
chance than:

="=SUM(A3:C8)"

It ain't the same. You just see the formula as text.

If your objective is to be able to specify the cells to sum via cells, use
INDIRECT.

=SUM(INDIRECT(D1))

This works because INDIRECT doesn't attempt to hand over what's IN A3:C8,
but returns a REFERENCE to A3:C8, and SUM takes that, fetches the contents
of those cells, and dutifully adds 'em up.

If it'd be easier to have separate extents, like "A3" in D1 and "C8" in D2,
you could use:

=SUM(INDIRECT(D1 & ":" D2))

You may want to take another approach. If the A3 extent is fixed, and you
have a varying count of cells, use OFFSET

=SUM(OFFSET(A3, 0, 0, C1, D1))

Where the range starts in A2, and C1 and D1 contain the count of rows (6)
and columns (3) respectively you wish to sum.