EXCEL: Executing Text as a Formula 2

[Aeneas]

A1 formula: =5
B1 formula: =6

C1 formula: ="A1*B1"
D1 formula: ?


What sort of function could I put in D that would "execute" or "calculate" or "interpret" the C1 text into a formula that would return 30?

I know how to do it in VBA, but is there a formula that would be like:

=REFERENCE(C1)
=EXECUTE(C1)
=FORMULA(C1)


I effectively want to take text and convert it into a calculable formula. Any help would be appreciated. Thanks!

 

[GlennUK]

You have to do it via a defined name, using an Excel4 macro command EVALUATE ... select D1, and do menu command Insert/Name/Define and define a name of EvalCellToLeft with a definition of:
=EVALUATE(C1)
click OK. Then in D1 type =EvalCellToLeft

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

aAFAIK there is nothing that does exactly that. Nearest you can get is the INDIRECT function.

An example:

With A1 = 5
B1 = 6
C1 = "A1"
D1 = "B1"

E1 = =INDIRECT(C1) * INDIRECT(D1)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MeGustaXL]

Darn It, Glenn! you just pipped me to it!

I was also gonna point Aeneas to Stephen Bullen's Formula charting method using EVALUATE:

http://www.bmsltd.ie/DLCount/DLCount.asp?file=ChtFrmla.zip

Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[xlbo]

nice - didn't know about that !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Yes MeGustaXL, I am fast little typer . I've used Stephen Bullen's site to re-learn how to do the formula charting method, that's a good example.

Hi Geoff ... there's life in the old dog yet ( that's me, by the way )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

...nor did I

==> *

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[GlennUK]

Thanks Skip , being an old timer I remember those Excel4 macro things.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

My MACRO days were in 1-2-3.

By the time I got Excel, 1994 I believe, it was VBA all the way!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[GlennUK]

So Skip, you're an old Lotus man ... so was I for a while.

I went to Excel in 1988, and it was Excel macros then ... and when I was doing development and support for a large user base, I had to program to the lowest common denominator ( e.g. if just one user still had Excel 3, I had to write code for Excel 3, which of course worked for the users with more modern versions ), all the way until 1999, and then was allowed to switch to VBA when the last user upgraded.

As you can imagine, the switch was a big step for me. ( I am still recovering ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.