Dynamic (?) formula

[Thore]

Hello all,

I do not know if dynamic is the right word because I did not find anything
applicable when searching the newsgroup...

In column A I have some data in form of numbers.
In cell B1 I would like to have a formula. The formula should be dependent
on what is entered in B2. For example if I enter 'sum' in B2, the B1 formula
should look like '=sum(A:A)'
If I then change B2 to 'count' then the B1 formula should be '=count(A:A)'
The solution sould also work on something like worksheet names being part of
a formula...

Rgds,
Thore

 

[Jason Morin]

It's not as easy as you'd think, and the best method
would be via VBA. In fact, I think Chip Pearson posted
UDF that achieves what you're looking for. However, you
could use:

=SUBTOTAL(VLOOKUP(LOWER(B2),
{"average",1;"count",2;"max",4;"min",5;"sum",9},2,0),INDIR
ECT(C2))

where B2 contains one of the 5 functions found in the
formula and C2 contains a column reference like "A:A" (no
quotes).

HTH
Jason
Atlanta, GA

 

[Thore]

Hi Jason,
thanks for the reply. I was trying to avoid VBA, but do you have the link
available (no idea what UDF stands for).

Unfortunately the solution you proposed does not work because I do not know
in advance what I will use later on. The whole thing is (in a first step)
supposed to work only as a quick "Ctrl-h replace" version. Thanks anyway.