Excel Define a range based on a cell value

Question

Is it possible to set a range's parameters based on a cell's value?

As an illustration, my choice is A1:A5. That's five cells, then. Can Excel make this determination by setting a cell value (say B1) to 5?

With just one modification in a cell value, it is designed to quickly change a large number of ranges. Thus, if I were to set cell value (B1) to 6, A1:A6 would be the new default range.

To be more precise, I want to do it the other way around.

Answer 1

Let's say that cells A1, A2, A3, A4, and A5 all contain the numbers 1, 2, 3, and 5, respectively. We compute the total of A1:Ax in cell A7. In cell B1, x is mentioned (in this case, x can be any number from 1 to 6). You can enter the formula below in cell A7:

=SUM(A1:INDIRECT(CONCATENATE("A",B1)))

CONCATENATE will give you the index of the cell Ax(if you put 3 in B1, CONCATENATE("A",B1)) gives A3).

INDIRECT convert "A3" to an index.