Using the contents of a ADDRESS formula as the cell range in a for

[Rose]

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

 

[Sheeloo]

Have you tried INDIRECT() formula?

If not then suppose you want to average the numbers in A1:A20 on Sheet2
and if you have this string in
B1 (of the sheet you have the formula) you have the string
Sheet2!a1:a20

then you can use
=AVERAGE(INDIRECT(B1))

Basically build the string containing the range address and pass it to
INDIRECT within AVERAGE...

 

[Chip Pearson]

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)

 

[T. Valko]

formulas in others (some of which are DIV/0 due copydown)

If there are errors they may cause the resulting formula to return an error.

Try this:

=AVERAGE(Data!B2:INDEX(Data!B2:B1000,MATCH(B3,Data!A2:A1000,0)))