CODEWITHSUNDEEP
excelexcel-formula
PP8
PP8

Reputation: 197

How can I expand a dynamic range with blanks between cells?

I want to define a dynamic range that will expand the rows and columns of my range whenever a new row or column is inserted. My current formula does not want to expand to cell $T$13. My headers start in row $M$7. How can I adjust my formula?

Formula Being Used

=OFFSET(Sheet1!$M$8,0,0,COUNTA(Sheet1!$M:$M),COUNTA(Sheet1!$1:$1))

I need my range to expand to cell $T$13

enter image description here

Upvotes: 1

Views: 679

Answers (2)

PP8
PP8

Reputation: 197

I ended up using the solution mentioned by @tevlyn.

In range$M2:$T2

I have the follolwing formula =IFERROR(MATCH(99^99,M$8:M$999,1),0).

I've added IFERROR because my data doesn't always have data stretched to $T2.

I then defined my range in name manager using:

=OFFSET(Sheet3!$M$8,0,0,MAX(Sheet3!$M$2:$T$2),COUNTA(Sheet3!$1:$1))

This still works even if there are blanks in between the range.

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Right now, your formula counts the number of text values in column M.

That is not a robust approach because column M contains only five text values, but columns S and T have many more values.

If you don't know which column may have the most number of entries, you can introduce a helper cell in each column that counts the number of entries below. I suggest you insert a new row 2. In column M, for example, put a formula in M2 

=counta($M$3:M$99999)

Copy that formula across to column T.

Next you can evaluate which of the columns has the largest number

=max(M2:T2)

This can be plugged into your original formula like this:

=OFFSET(Sheet1!$M$8,0,0,max(M2:T2),COUNTA(Sheet1!$1:$1))

So now, instead of just looking at how many rows are in column M, the formula uses the maximum number of rows in the columns M to S.

You can now hide row 2 if it upsets your worksheet design.

Edit: the mere count of text values with CountA will ignore blank cells and will return incorrect results. You really need a formula to find the row number of the last populated cell in each column.

This should really be a new question, but here goes

If the column has number values you can use

=MATCH(99^99,B5:B999,1)

If the column has text values you can use

=MATCH("zzz",C5:C999,1)

Adjust your ranges accordingly.

Upvotes: 2

Related Questions