execute string from a cell

[dmonder]

I want to be able to enter a function in one cell as a text string and
be able to use that function in another cell.

For example, I would enter the string "%CELL% - MIN(%RANGE%)" into
B1. The cells B2 - B5 would replace the phrase %CELL% with their
respective cell value and %RANGE% with B2 through the last populated
cell.

I can get Excel to replace %CELL% and %RANGE% but I cannot get it to
execute the resulting string. I used SUBSTITUTE as follows:

=SUBSTITUTE(SUBSTITUTE($B$1,"%CELL%",ADDRESS(ROW(),COLUMN())),"%RANGE
%",ADDRESS(2,COLUMN())&":"&ADDRESS(2+COUNTA(A:A),COLUMN()))

I get this:

Function:,'Sheet 1'!%CELL% - MIN('Sheet 1'!%RANGE%)
Program 1,='Sheet 1'!$B$2 - MIN('Sheet 1'!$B$2:$B$5)
Program 2,='Sheet 1'!$B$3 - MIN('Sheet 1'!$B$2:$B$5)
Program 3,='Sheet 1'!$B$4 - MIN('Sheet 1'!$B$2:$B$5)
Program 4,='Sheet 1'!$B$5 - MIN('Sheet 1'!$B$2:$B$5)

They may look like formula's in B2 - B5 but they are text strings.
How can I now execute them?

David

 

[Guest]

Take a look at the INDIRECT function. I think that will give you what you
are looking for.

 

[Pete_UK]

You need a user-defined function like this:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

and then use it like this:

=eval(B2)

Hope this helps.

Pete

 

[dmonder]

You need a user-defined function like this:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

and then use it like this:

=eval(B2)

Hope this helps.

Pete

Is there a way to do this without a user defined function? The
problem is that this worksheet will be used by people that may find it
uncomfortable to get an prompt to allow a script to run. If I can get
this to work as a cell function, then that prompt will not come up.

INDIRECT does not work. I get a #REF! error.

David

 

[Pete_UK]

INDIRECT won't work for this - you can build up cell and range
references from strings using it, but you can't execute a string that
looks like a function with it.

I don't know of a non-VBA way that will help you to do what you
want ...

.... but others might!

Pete

 

[Guest]

How about Text to Columns?

If it is desired to convert the string into a formula rather than into a
value, then if A1 contains:
=1+2 as a string will be converted into the formula
=1+2 in either the same cell or a different cell.

 

[dmonder]

How about Text to Columns?

If it is desired to convert the string into a formula rather than into a
value, then if A1 contains:
=1+2 as a string will be converted into the formula
=1+2 in either the same cell or a different cell.

I don't understand what you mean? Can you give more details?

David

 

[RagDyer]

Are you copying the Substitute formula to itself and then paste special as
"Values" first?
Then TTC.