Referring to a cell by entering an integer; RANGE functions
 

I need to grab the contents of a cell and put it into a formula. I would
have a fixed location into which I type an integer (say, 23). In another cell
would be a formula that would use the contents of B23, as in B23/12. The B
is a constant, as in $B[integer_in_fixed_location].

What's going on is I have two long columns of numbers, A1 and B1 through A200
and B200. I'm looking for ways to look at numbers within a range of rows. I
can easily hand-key in the range of interest, but I want to have two cells
into which I can type in a stop and stop row.

Any ideas? thanks.

Referring to a cell by entering an integer; RANGE functions
 

On Sat, 24 May 2008 15:15:03 -0400, Tim Murray
wrote:

I need to grab the contents of a cell and put it into a formula. I would
have a fixed location into which I type an integer (say, 23). In another cell
would be a formula that would use the contents of B23, as in B23/12. The B
is a constant, as in $B[integer_in_fixed_location].

What's going on is I have two long columns of numbers, A1 and B1 through A200
and B200. I'm looking for ways to look at numbers within a range of rows. I
can easily hand-key in the range of interest, but I want to have two cells
into which I can type in a stop and stop row.

Any ideas? thanks.


If you fixed location is C1 you can use the formula INDEX(B1:B200,C1)
wherever needed. If C1 holds 23, then INDEX(B1:B200,C1) gives the
value in cell B23.

Hope this helps. / Lars-Åke

Referring to a cell by entering an integer; RANGE functions
 

On Sat, 24 May 2008 19:35:26 GMT, Lars-Åke Aspelin
wrote:

On Sat, 24 May 2008 15:15:03 -0400, Tim Murray
wrote:

I need to grab the contents of a cell and put it into a formula. I would
have a fixed location into which I type an integer (say, 23). In another cell
would be a formula that would use the contents of B23, as in B23/12. The B
is a constant, as in $B[integer_in_fixed_location].

What's going on is I have two long columns of numbers, A1 and B1 through A200
and B200. I'm looking for ways to look at numbers within a range of rows. I
can easily hand-key in the range of interest, but I want to have two cells
into which I can type in a stop and stop row.

Any ideas? thanks.


If you fixed location is C1 you can use the formula INDEX(B1:B200,C1)
wherever needed. If C1 holds 23, then INDEX(B1:B200,C1) gives the
value in cell B23.

Hope this helps. / Lars-Åke

And if you want a range in the B column with the first and last row
given by the contents of cells C1 and C2 respectively you can use
OFFSET(B1,C1-1,0,C2-C1+1,1)

Lars-Åke

Referring to a cell by entering an integer; RANGE functions
 

On Sat, 24 May 2008 15:15:03 -0400, Tim Murray wrote:

I need to grab the contents of a cell and put it into a formula. I would
have a fixed location into which I type an integer (say, 23). In another cell
would be a formula that would use the contents of B23, as in B23/12. The B
is a constant, as in $B[integer_in_fixed_location].

What's going on is I have two long columns of numbers, A1 and B1 through A200
and B200. I'm looking for ways to look at numbers within a range of rows. I
can easily hand-key in the range of interest, but I want to have two cells
into which I can type in a stop and stop row.

Any ideas? thanks.


=INDIRECT(ADDRESS(cell_with_row_number,2))

If cell_with_row_number contains 23, the above will return the contents of B23
--ron

Referring to a cell by entering an integer; RANGE functions
 

On Sat, 24 May 2008 15:44:19 -0400, Ron Rosenfeld wrote:
On Sat, 24 May 2008 15:15:03 -0400, Tim Murray wrote:

I need to grab the contents of a cell and put it into a formula. I would
have a fixed location into which I type an integer (say, 23). In another
cell
would be a formula that would use the contents of B23, as in B23/12. The B
is a constant, as in $B[integer_in_fixed_location].

What's going on is I have two long columns of numbers, A1 and B1 through
A200
and B200. I'm looking for ways to look at numbers within a range of rows.
I
can easily hand-key in the range of interest, but I want to have two cells
into which I can type in a stop and stop row.

Any ideas? thanks.


=INDIRECT(ADDRESS(cell_with_row_number,2))

If cell_with_row_number contains 23, the above will return the contents of
B23
--ron

Thanks. A combination of your and Lars's answers were utilized.

Referring to a cell by entering an integer; RANGE functions
 

On Sat, 24 May 2008 15:40:57 -0400, Lars-Åke Aspelin wrote:

And if you want a range in the B column with the first and last row given
by the contents of cells C1 and C2 respectively you can use
OFFSET(B1,C1-1,0,C2-C1+1,1)

Lars-Åke


Thanks. A combination of your and Ron's answers were utilized.