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?

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?

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?

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?

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?