Is it possible to call an excel function indirectly?
 

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?

Is it possible to call an excel function indirectly?
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Alban???" wrote:

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?

Is it possible to call an excel function indirectly?
 

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 C34:P34...

Function IndirectF(R As Range)

x = Evaluate("" & R & "(C34:P34)")

End Function

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

Cell Q34 returns "0"

However the =AVERAGE(C34:P34) returns "15.96".

Something's amis.... Any ideas ?

Thanks Alban


"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Alban???" wrote:

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?

Is it possible to call an excel function indirectly?
 

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

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

HTH
Elkar


"Alban???" wrote:

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 C34:P34...

Function IndirectF(R As Range)

x = Evaluate("" & R & "(C34:P34)")

End Function

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

Cell Q34 returns "0"

However the =AVERAGE(C34:P34) returns "15.96".

Something's amis.... Any ideas ?

Thanks Alban


"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Alban???" wrote:

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?

Is it possible to call an excel function indirectly?
 

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


"Alban???"

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?