Is it possible to call an excel function indirectly?

[Alban???]

Is it possible to use a referenced text cell in Excel to call up functions
form the cells that reference this text?

For example if in Cell A1 I have the text "Mode" can I use another cell say
C2 to reference that cell and produce the mode of a named range? And then, if
I were to change A1 to "Average" for C2 to display the average of the named
range?

 

[Shane Devenshire]

Hi,

Here is a VBA function to do what you want:

Function IndirectF(R As Range)
x = Evaluate("" & R & "(C1:C4)")
End Function

In the spreadsheet you want to calculate something for the range C1:C4
(change this to a range name if you want). In A1 enter AVERAGE, in another
cell enter =IndirectF(A1)

As you change the word in A1 the calculation will change.

 

[Alban???]

Dear Shane,
Thank you for the reply, but what I tried didn't work.

I probaby did something wrong so I'll explain the steps I took.

1) I copied your code below into developer modulde code and edited as I
wanted to sum the range C3434...

Function IndirectF(R As Range)

x = Evaluate("" & R & "(C3434)")

End Function

2) in Cell Q34 typed: =IndirectF(Q33), where cell Q33= Average

Cell Q34 returns "0"

However the =AVERAGE(C3434) returns "15.96".

Something's amis.... Any ideas ?

Thanks Alban

 

[Elkar]

A non-VBA route would be to use an IF statement.

=IF(A1="Mode",MODE(YourRange),IF(A1="Average",AVERAGE(YourRange),IF(A1="Sum",SUM(YourRange))))

HTH
Elkar

 

[Jim Cone]

Seems like something to avoid... it crashed XL 97 when I tried it.
--
Jim Cone
Portland, Oregon USA


"Alban???"
<[email protected]>
wrote in message
Is it possible to use a referenced text cell in Excel to call up functions
form the cells that reference this text?

For example if in Cell A1 I have the text "Mode" can I use another cell say
C2 to reference that cell and produce the mode of a named range? And then, if
I were to change A1 to "Average" for C2 to display the average of the named
range?