Excel Define a range based on a cell value

Question

Is it possible to define a range based on a value given in a cell.

So, for example: My selection is A1:A5 That are five cells. is it possible to let excel determine this by setting a cell value (like B1) to 5.

It for the purpose of easily changes a lot of ranges with one change in a cell value. So if I would change the cell value (B1) to 6. The range would automatically change to A1:A6

Even more specific, I would like to do it reversed.

Final example: Selection should be A6:A10 (this are the five cells). In B1 I have the value 5. If I change the value of B1 to 6. The range should change to A5:A10

Could somebody help me???

Answer 1

Say you have number 1,2,3,4,5,6, in cell A1,A2,A3,A4,A5,A6 respectively. in cell A7 we calculate the sum of A1:Ax. x is specified in cell B1 (in this case, x can be any number from 1 to 6). in cell A7, you can write the following formular:

=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 a index.

see this link Using the value in a cell as a cell reference in a formula?

Answer 2

You can also use OFFSET:

OFFSET($A$10,-$B$1+1,0,$B$1)

It moves the range $A$10 up by $B$1-1 (becomes $A$6 ($A$5)) and then resizes the range to $B$1 rows (becomes $A$6:$A$10 ($A$5:$A$10))

Answer 3

Here is an option. It works by using making an INDIRECT(ADDRESS(...)) from the ROW and COLUMN of the start cell, A1, down to the initial row + the number of rows held in B1.

SUM(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))):INDIRECT(ADDRESS(ROW(A1)+B1,COLUMN(A1))))

A1: is the start of data in a the "A" column

B1: is the number of rows to sum

Answer 4

This should be close to what you are looking for your first example:

=SUM(INDIRECT("A1:A"&B1,TRUE))

This should be close to what you are looking for your final example:

=SUM(INDIRECT("A"&1+B1&":A"&B1,TRUE))

Answer 5

Based on answer by @Cici I give here a more generic solution:

=SUM(INDIRECT(CONCATENATE(B1,C1)):INDIRECT(CONCATENATE(B2,C2)))

In Italian version of Excel:

=SOMMA(INDIRETTO(CONCATENA(B1;C1)):INDIRETTO(CONCATENA(B2;C2)))

Where B1-C2 cells hold these values:

• A, 1
• A, 5

You can change these valuese to change the final range at wish.

---

Splitting the formula in parts:

• SUM(INDIRECT(CONCATENATE(B1,C1)):INDIRECT(CONCATENATE(B2,C2)))
• CONCATENATE(B1,C1) - result is A1
• INDIRECT(CONCATENATE(B1,C1)) - result is reference to A1

Hence:

=SUM(INDIRECT(CONCATENATE(B1,C1)):INDIRECT(CONCATENATE(B2,C2)))

results in

=SUM(A1:A5)

---

I'll write down here a couple of SEO keywords for Italian users:

• come creare dinamicamente l'indirizzo di un intervallo in excel
• formula per definire un intervallo di celle in excel.

Con la formula indicata qui sopra basta scrivere nelle caselle da B1 a C2 gli estremi dell'intervallo per vedelo cambiare dentro la formula stessa.