Can I use variables for workheet name references in Excel functions?
 

I'm working with Excel 2003 and trying to look for a way to create some sort
of dynamic references in functions between worksheets in the same workbook.
Let me try to explain -

Say I have a workbook with 12 different worksheets with the names -
January, February, March, Etc...

On a new Worksheet I want to write a function that will use a cell as a
reference for the worksheet to use in the function.
I'm going to have a function in that cell that will return a name (In this
example - Cell A2)
In Another cell, I want to write a function that will retrieve the highest
value from a certain column (in this Example - Column C) from the worksheet
with the name in cell A2.

I was thinking that I could use a function that would look something like
this -

=max($A$2!C:C)
When Cell A2 will say - April, I'll get the heighest value from Column C on
Worksheet "April", when It'll say - June, from worksheet "June", etc...

I get an error message for this function and it will only work for me if I
use a constant value. for example -
=max(APRIL!C:C)

Is there a way to use variables for worksheet names in functions in Excel?
How should I write this function?

One way

=MAX(INDIRECT("'"&A2&"'!C2:C100"))


will return max value from the sheet that is in A2 and range C2:C100

--

Regards,

Peo Sjoblom


"Amihai Bareket" wrote in message
...
I'm working with Excel 2003 and trying to look for a way to create some
sort
of dynamic references in functions between worksheets in the same
workbook.
Let me try to explain -

Say I have a workbook with 12 different worksheets with the names -
January, February, March, Etc...

On a new Worksheet I want to write a function that will use a cell as a
reference for the worksheet to use in the function.
I'm going to have a function in that cell that will return a name (In this
example - Cell A2)
In Another cell, I want to write a function that will retrieve the highest
value from a certain column (in this Example - Column C) from the
worksheet
with the name in cell A2.

I was thinking that I could use a function that would look something like
this -

=max($A$2!C:C)
When Cell A2 will say - April, I'll get the heighest value from Column C
on
Worksheet "April", when It'll say - June, from worksheet "June", etc...

I get an error message for this function and it will only work for me if I
use a constant value. for example -
=max(APRIL!C:C)

Is there a way to use variables for worksheet names in functions in Excel?
How should I write this function?