Remember Me?

#1
July 19th 11, 11:45 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 162
Changing function name in formula

I'm trying to create a formula that will allow changing the
function used.

For example:

A B
1 2 Product
2 3
3
4 =B1(A1:A2)

=B1(A1:A2) should evaluate to 6.

How can I get B1 to not be evaluated as text ?

- Ronald K.

#2
July 20th 11, 08:04 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 3
Changing function name in formula

Hi Ronald,

Place the cursor in B1 cell and press Alt+E+A+F and copy and the paste
or type the below formula B1 cell

=Sum(A1:A2)

Or
=A1+A2

The above formula will evaluate the result as 5.

Hope that helps!

---

Sixthsense

On Jul 20, 3:45*am, kittronald wrote:
* *I'm trying to create a formula that will allow changing the
function used.

* *For example:

* * * * A * * * B
1 * * * 2 * * * Product
2 * * * 3
3
4 * * * =B1(A1:A2)

* *=B1(A1:A2) should evaluate to 6.

* *How can I get B1 to not be evaluated as text ?

- Ronald K.

#3
July 20th 11, 09:02 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 620
Changing function name in formula

I think the OP wanted the product, not the sum.
=PRODUCT(A1:A2) or =A1*A2 will give the product.

David Biddulph

On 20/07/2011 08:04, Sixthsense wrote:
Hi Ronald,

Place the cursor in B1 cell and press Alt+E+A+F and copy and the paste
or type the below formula B1 cell

=Sum(A1:A2)

Or
=A1+A2

The above formula will evaluate the result as 5.

Hope that helps!

---

Sixthsense

On Jul 20, 3:45 am, wrote:
I'm trying to create a formula that will allow changing the
function used.

For example:

A B
1 2 Product
2 3
3
4 =B1(A1:A2)

=B1(A1:A2) should evaluate to 6.

How can I get B1 to not be evaluated as text ?

- Ronald K.

#4
July 20th 11, 12:35 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 587
Changing function name in formula

hi,

=EvaluateString(B1&"(A1:A2)")

Function EvaluateString(tString As String)
EvaluateString = Evaluate(tString)
End Function

cell B1 can contain a function like
Product, Sum, Max, Min, etc..

--
isabelle

#5
July 20th 11, 11:01 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 162
Changing function name in formula

Isabelle,

Thanks for the response and understanding what I was trying to
accomplish.

For calculating one cell, your solution works.

Is there a way to modify it so that I can Fill Down or Fill Right.

For example, if I were to fill right ...

=EvaluateString(\$B\$1&"(A1:A2)")

it should look like ...

=EvaluateString(\$B\$1&"(B1:B2)").

- Ronald K.

#6
July 20th 11, 11:49 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 162
Changing function name in formula

Isabelle,

Figured it out.

=EvaluateString(\$B\$1)(A1:A2)

You're The Man ... I mean you're The Woman.

Thanks for the help.

- Ronald K.
#7
July 21st 11, 12:04 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 86
Changing function name in formula

You have tested this thoroughly?

Gord Dibben Microsoft Excel MVP

On Wed, 20 Jul 2011 15:49:26 -0700 (PDT), kittronald
wrote:

Isabelle,

Figured it out.

=EvaluateString(\$B\$1)(A1:A2)

You're The Man ... I mean you're The Woman.

Thanks for the help.

- Ronald K.

#8
July 21st 11, 01:23 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 587
Changing function name in formula

wow, i can confirm that neni ;-)

--
isabelle

Le 2011-07-20 19:04, Gord a écrit :
You have tested this thoroughly?

#9
July 21st 11, 01:46 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 587
Changing function name in formula

hi kittronald,

=EvaluateString(\$B\$1&"(B1:B2)").

the cell "B1" can not be do "function name" and be a part of the evaluation

--
isabelle

#10
July 21st 11, 05:51 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 162
Changing function name in formula

Isabelle,

<Sound of air leaving balloon

Ok, so I'm back to square one.

I was using a different, third party function, that when evaluated,
mysteriously returned a number - which led me to believe the correct
value was being returned.

Basically, I'm trying to mass replace a function without resorting
to using a macro that performs a Search and Replace.

If B1 were the linked cell of a ComboBox that had multiple function
names, how could I achieve the initial solution ?

And how did I earn the title of a Hungarian aunt ?

- Ronald K.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post JessLRC Excel Worksheet Functions 0 April 20th 10 03:10 PM Jay Excel Worksheet Functions 2 March 18th 08 02:11 PM Cheese Excel Discussion (Misc queries) 7 March 19th 07 12:51 AM Neo1 Excel Worksheet Functions 4 January 17th 06 09:09 AM Tucson Guy Excel Discussion (Misc queries) 1 December 19th 04 09:47 AM

All times are GMT +1. The time now is 10:53 PM.