Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the contents of a ADDRESS formula as the cell range in a for
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the contents of a ADDRESS formula as the cell range in a for
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... "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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the contents of a ADDRESS formula as the cell range in a for
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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the contents of a ADDRESS formula as the cell range in a for
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))) -- Biff Microsoft Excel MVP "Rose" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a specific address from a list if the contents of cell A1=x | Excel Worksheet Functions | |||
Insert Cell Contents in middle of web address | Excel Discussion (Misc queries) | |||
How do I use cell contents as an address in a formula | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |