use the contents of a cell in a function ... ??
 

I have a workbook with 22 worksheets. Each sheet is named for an
author, say `Able' through ... `Yangtze.' One the front sheet, I have
the list of authors in a column. How do I write the MAX, AVERAGE, and
MEDIAN commands so that I do not have to rewrite the names of the
worksheets in each cell, ie:

=AVERAGE(`THE NAME IN COLUMN A!$B$2:$B$1000)

thanks in advance.

use the contents of a cell in a function ... ??
 

Use the INDIRECT() function.
Look in HELP for details or use the function wizard.

--
Kind regards,

Niek Otten

"LarryLev" wrote in message
oups.com...
I have a workbook with 22 worksheets. Each sheet is named for an
author, say `Able' through ... `Yangtze.' One the front sheet, I have
the list of authors in a column. How do I write the MAX, AVERAGE, and
MEDIAN commands so that I do not have to rewrite the names of the
worksheets in each cell, ie:

=AVERAGE(`THE NAME IN COLUMN A!$B$2:$B$1000)

thanks in advance.

use the contents of a cell in a function ... ??
 

Try:

=AVERAGE(INDIRECT(A1&"!B2:B1000"))

where the sheet name is in cell A1

Hope this helps
Rowan

LarryLev wrote:
I have a workbook with 22 worksheets. Each sheet is named for an
author, say `Able' through ... `Yangtze.' One the front sheet, I have
the list of authors in a column. How do I write the MAX, AVERAGE, and
MEDIAN commands so that I do not have to rewrite the names of the
worksheets in each cell, ie:

=AVERAGE(`THE NAME IN COLUMN A!$B$2:$B$1000)

thanks in advance.

use the contents of a cell in a function ... ??
 

=AVERAGE(INDIRECT(A11 &"!D3:D5"))
Of course, adjust the range...
=AVERAGE(INDIRECT(A11 & "!$B$2:$B$1000"))

"LarryLev" wrote:

I have a workbook with 22 worksheets. Each sheet is named for an
author, say `Able' through ... `Yangtze.' One the front sheet, I have
the list of authors in a column. How do I write the MAX, AVERAGE, and
MEDIAN commands so that I do not have to rewrite the names of the
worksheets in each cell, ie:

=AVERAGE(`THE NAME IN COLUMN A!$B$2:$B$1000)

thanks in advance.

use the contents of a cell in a function ... ??
 

thanks all. perfect