range.formula

**Osmar Mateus** · 11-02-2007, 03:31 PM

I need to solve this problem:
In a cell named item I have a text "unit price".
In a cell named unitprice I have a number 3.45.
In a cell named linetext I have a text "=concatenate(item;unitprice)"
Is it possible to use range.formula to do this using VBA? Is there another way to do this?
I need this type of solution because the formulas in linetext will change based in other cells.

Thanks a lot.

**Tarball** · 11-02-2007, 03:47 PM

Of course this is possible and its pretty trivial too, simply concantenate the values of each cell or apply a formula..do whatever.

If you have an example spreadsheet, please post it and explain where you would like to see the change.

**Excelenator** · 11-02-2007, 03:50 PM

Originally Posted by Tarball

Of course this is possible and its pretty trivial too, simply concantenate the values of each cell or apply a formula..do whatever.

You really shouldn't be condescending to people who are asking for help.

**Osmar Mateus** · 11-02-2007, 04:40 PM

I am sorry because I have dificuties to expres myself in english.

The problem is that the concatenated formula is just an example. The content of this cell will keep changing. Next time, maybe the content will be =unitprice * quantity.

I would like to know if it is a way of executing a formula that is inside a cell as a text.

Thanks for your help.

**shg** · 11-02-2007, 04:58 PM

Yes, you can evaluate a text formula.

Select cell B1.
Do Insert > Name > Define, create the name Eval (or anything else you want to call it), and in RefersTo enter =Evaluate(A1)
Now put a text formula in some cell, say F2: =5^3. Note the leading apostrophe (or you could format the cell as text before entering). That's pretty mindless, but any valid Excel formula will work.
In the next cell to the right, enter =Eval

Presto, 125!

Here's what's entered:

  -F-- --G--
2 =5^3 =Eval

Here's what shows up

  -F-- -G-
2 =5^3 125

So wherever you enter it, =Eval will evaluate the text formula to its left. Pretty neat, huh?

**Excelenator** · 11-02-2007, 05:29 PM

Ok so, if I understand this correctly, Osmar is trying to evaluate a formula in a cell that is the result of a concatenation formula of actual named ranges?

I have attached a file with what I interpret this to be. The only problem that I am having is that the =Eval is not volatile, meaning it doesn't automatically recalcualte with the spreadsheet. Is this normal behavior?