Referring to a cell by entering an integer; RANGE functions

[Tim Murray]

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.

 

[Lars-Åke Aspelin]

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

 

[Lars-Åke Aspelin]

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

 

[Ron Rosenfeld]

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

 

[Tim Murray]

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.

 

[Tim Murray]

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.