I don't quite understand what you're getting at, but if you already
have the address (as a string) you can use INDIRECT to convert that to
a cell reference and pass that to AVERAGE. E.g.,

=AVERAGE(INDIRECT("the address string"))

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 13 Mar 2009 10:27:06 -0700, Rose
wrote:

We have a workbook, 2 spreadsheets - 1 data, 1 report. On the report is the
date in B3, will be typed in manually each time. On the data sheet are many
columns, the first of which is the date for the calendar year. In other
columns there are numbers that are typed in, and formulas in others (some of
which are DIV/0 due copydown).

What we want to do is to get the cell range for data!A2:Athe cell that
contains report!B3, and average the contents of, say column B for those same
rows.

We've found various ways of getting the mysterious and dynamic cell ref for
the final cell - CELL, OFFSET, etc, but we can't get the contents (not the
value) into another formula.

I found a formula at http://www.cpearson.com/excel/excelF.htm that makes
getting the cell range tidy, since the date will also be typed into Awhatever
each time, so that's good. But we're stymied on how to get the calculated
cell range to be the cell range in an averaging formula.

Thanks!!