Excel Define a range based on a cell value

excel range cell
user3491168 picture user3491168 · Apr 2, 2014 · Viewed 114.7k times · Source

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

Cici picture Cici · Apr 2, 2014

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?

Related questions

Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Detect if range is empty

I want to check if a range in Excel is empty. How do I write in VBA code: If Range("A38":"P38") is empty

Read More
VBA paste range

I have simple goal of copying range and pasting it into another spreadsheet. The following code below gives copies, but does not paste. Sub Normalize() Dim Ticker As Range Sheets("Sheet1").Activate Set Ticker = Range(Cells(2, 1), Cells(65, 1)) Ticker.Copy Sheets("…

Read More