Using a formula as a cell reference

[ripstaur]

This seems like it would be simple...I'm trying to find a way to use a the results of a function as a cell reference in a range. For instance, if I have a column of 25 numbers, say B1:B25, and I want to select a range from B1 to a row specified by another function--let's say the result from C1--then how do I use that in my reference? What I want to do, in other words, is have Excel perform a function on the range from B1:B(result in C1). So if C1 is 14, the range is B1:B14; if C1 is 18, the range is B1:B18, etc.
I've tried using various ADDRESS, LOOKUP and OFFSET functions, but it doesn't seem to matter. Things like SUM B1:ADDRESS(14,11,4) and COUNTIF(B1:B(LOOKUP K13, K1:K6),3) don't work; of course the obvious and intuitive COUNTIF(B1:B(C1)) doesn't work, either. It seems as though this would not be this hard, and it probably isn't...I just can't find an answer in any of the "help" files.

 

[Marcol]

Does this help?
Change the value in C1 to Sum from B1 to B&C1

OFFSET(B1,0,0,C1,1) is the range.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00; FONT-WEIGHT: bold">14</TD><TD style="TEXT-ALIGN: right">127</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: right">4</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD style="TEXT-ALIGN: right">8</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD style="TEXT-ALIGN: right">9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD style="TEXT-ALIGN: right">12</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD style="TEXT-ALIGN: right">13</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD style="TEXT-ALIGN: right">14</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD style="TEXT-ALIGN: right">21</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD style="TEXT-ALIGN: right">22</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD style="TEXT-ALIGN: right">23</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD style="TEXT-ALIGN: right">24</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD style="TEXT-ALIGN: right">26</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD> </TD><TD style="TEXT-ALIGN: right">27</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD> </TD><TD style="TEXT-ALIGN: right">28</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD> </TD><TD style="TEXT-ALIGN: right">21</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD> </TD><TD style="TEXT-ALIGN: right">22</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD> </TD><TD style="TEXT-ALIGN: right">23</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD> </TD><TD style="TEXT-ALIGN: right">24</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD> </TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=SUM(OFFSET(B1,0,0,C1,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[T. Valko]

This seems like it would be simple...I'm trying to find a way to use a the results of a function as a cell reference in a range. For instance, if I have a column of 25 numbers, say B1:B25, and I want to select a range from B1 to a row specified by another function--let's say the result from C1--then how do I use that in my reference? What I want to do, in other words, is have Excel perform a function on the range from B1:B(result in C1). So if C1 is 14, the range is B1:B14; if C1 is 18, the range is B1:B18, etc.
I've tried using various ADDRESS, LOOKUP and OFFSET functions, but it doesn't seem to matter. Things like SUM B1:ADDRESS(14,11,4) and COUNTIF(B1:B(LOOKUP K13, K1:K6),3) don't work; of course the obvious and intuitive COUNTIF(B1:B(C1)) doesn't work, either. It seems as though this would not be this hard, and it probably isn't...I just can't find an answer in any of the "help" files.

Try this something like this...

To get the sum of the range...

=SUM(B1:INDEX(B1:B25,C1))

Note that if C1 is an empty cell the formula will calculate the entire range.

 

[ripstaur]

Marcol,

Thank you!!! I knew this had to be simple. I had tried the OFFSET function, but must have made some bad entries. Thanks again...this will do what I need!
ray:

 

[Marcol]

Remeber that OFFSET() is a volatile function and can seriously compromise the efficiency of your workbook when the data range is large.
I offered this purely to illustrate how to define a range using this function.