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?
|