how do I do a variable length array based on the value in a cell

M

Mark Pranger

I want to create a cell range that will be based on the value of a cell so
that i can change the value of the cell and the range will lengthened or
shortened. Say I want to have the range start at cell b1 and go down the
column for 10 cells. then I decide to change it to 15 cells and then 40
cells. But I don't want to have to rewrite the formula each time. Any
suggestions?
 
M

Max

Think OFFSET could be used,
with the height param pointing to a cell.

For eg, with the height param number in A1, say: 5
=OFFSET($B$1,,,A1)
would return the range B1:B5

If A1 contains 10, then the range B1:B10 is returned
And so on
 
Top