Dynamic Range over extending issue

[xluser2007]

Hi All,

I have attached a reference workbook.

In it I have tried to define a dynamic named range called "data".

I want it to extend dynamically across and down from Cell A10. With the given data, I have used the formula, in the data worksheet:


=OFFSET(data!$A$10,0,0,COUNTA(data!$A$10:$A$65536),COUNTA(data!$10:$10))

Even though rows L, M and N are empty, the dynamic range actually goes out to column Y.

Could anyone please explain why the above formula is not stopping at Column L in this case ? (please note there may be data in cols M, N etc, so I would like the formula to extend dynamically across).

regards?

[xluser2007]

here is the workbook.

[xluser2007]

Just an update,

The following formula works for the xample at hand, but is not dynamic as it forcibly truncates the column length to 11.


=OFFSET(data!$A$10,0,0,COUNTA(data!$A$10:$A$65536),11)

Is there a way to make the column ref in the offset formula above dynamic?

[mdmackillop]

=OFFSET(data!$A$10,0,0,COUNTA(data!$A:$A),COUNTA(data!$A$10:$N$10))

[Bob Phillips]

It is because there is data in columns beyond column A, and the COUNTA($10:$10) returns 25, the number of columns with data, and so extends 25 columns, Y.

You need to restrict the column count to those columns that you are interested in

=OFFSET(data!$A$10,0,0,COUNTA(data!$A$10:$A$65536),COUNTA(data!A$10:N$10))

I would also be a bit smarter, although more long-winded, with the rows

=OFFSET(data!$A$10,0,0,COUNTA(data!$A:$A)-COUNTA($A$1:$A$9),COUNTA(data!A$10:N$10))

[xluser2007]

=OFFSET(data!$A$10,0,0,COUNTA(data!$A:$A)-9,COUNTA(data!$A$10:$N$10))
Malcolm, many thanks, worked very nicely indeed.


It is because there is data in columns beyond column A, and the COUNTA($10:$10) returns 25, the number of columns with data, and so extends 25 columns, Y.

You need to restrict the column count to those columns that you are interested in

=OFFSET(data!$A$10,0,0,COUNTA(data!$A$10:$A$65536),COUNTA(data!A$10:N$10))

I would also be a bit smarter, although more long-winded, with the rows

=OFFSET(data!$A$10,0,0,COUNTA(data!$A:$A)-COUNTA($A$1:$A$9),COUNTA(data!A$10:N$10))
Bob, your formula worked a treat, as expected.

I am stil perplexed why the original formula doesn't still cause the range to be limited to column K (as required), that is the last non-blank column that offset should encounter when going along the row, right (and hence stop there)?

Also, given your explanation above, I'm not quite understanding why doing the $10:$10 for the columns causes Excel to go out to column Y, and not to column AD in the workbook attached? Could you please clarify.

Lastly, say i want to define the dynamic range starting at Z7 and extending across and down dynamically, is this the most rigorous way to go:


=OFFSET(data!$Z$7,0,0,COUNTA(data!$Z:$Z)-COUNTA(data!$Z$1:$Z$6),COUNTA(data!$7:$7)-COUNTA(data!$A$7:$Y$7))
It seems to work, but I'm not quite sure why because of the above questions.

Thanks again to both of you for your help.

regards,

[Bob Phillips]

Because COUNTA counts the number of cells with something in them, not the number of cells. So COUNTA(10:10) returns 25, the number of cells with data. Then OFFSET goes that number of columns to the right of the base cell, not to the last column that contains data.

OFFSET is not going anywhere, it determines nothing itself, it offsets the number of rows and colums that you tell it, and includes the number of rows and columns that you tell it, you said 25 so it went that far, no further, and no less.

Beware of using Malcolm's formula:oops: . If you enter values in A1:A9, you will get a truncated dataset in the OFFSET formula.

[mdmackillop]

There is an error in my formula. Delete the -9 or the range misses the last 9 rows. XLD's "smart" code allows for any data above row 10.

Offset is not like the VBA end(xltoright). You are using COUNTA to count anf filled cells in a range. Try it as formulae in the worksheet to see the differences
=COUNTA(10:10)
=COUNTA(A10:N10)

[Bob Phillips]

On your second point, that formula certainly gets the correct data, with or without extra data in Z1:Z6, A7:Y7.

Be aware that OFFSET does rely on the content of the cells. You have seen one issue where there are extra cells populated, but you formula has a problem if cell Z7 is empty, which if it is the intersect of headings it might well be, because it wll omit a row and a column.

[xluser2007]

Bob, Malcolm.

Sincere thanks for your persistence with this issue and for helping me to understand excel.

I now apprecaite that it is the counta that is guiding the offset, and hence forcing the dynamic range.

As per Malcolms suggestion, I tested the 2 formulae:

=COUNTA(A10:N10), gave 11 as expected number of non blank cells in the given range.

=COUNTA(10:10), gave 25

Now I get it, the offset thus went along 25 i.e number of non-blanks and happened to end at column Y as a result. I was confusing earlier and thinking that it was not extending out dynamically to the contagious ranges in columns Z:AD.

OK, now to the last part:


On your second point, that formula certainly gets the correct data, with or without extra data in Z1:Z6, A7:Y7.

Be aware that OFFSET does rely on the content of the cells. You have seen one issue where there are extra cells populated, but you formula has a problem if cell Z7 is empty, which if it is the intersect of headings it might well be, because it wll omit a row and a column.

Bob, sorry, I;m a little confused, could you please suggest the best formula to use (why the A7:Y7 reference used in the way that it has works is baffling me a bit).

Thanks again sincerely to both of you.

[Bob Phillips]

I'm back, lunch break.

To demonstrate it, let's get testy.

Load B1:E1 with Product1, Product2, Product3, and Product4
Load A2:A5 with 2005, 2006, 2007, 2008.

Load B2:E5 with 1,2,3,..., 15, 16

Note that A1 is blank!

You see the idea, we have variable number of product and a variable number of years. We want a formula to calculate the total over all products over all years. Of course, in this example, we could just use SUM(B:IV), but's let us assume that there is other data elsewhere on the spreadsheet, and we don't want to include that.

For this example, I am ignoring the issue about previous data in the rows or columns, I have started at A1 to illustrate the point I made earlier about headings.

So the obvious dynamic range formula is

=SUM(OFFSET($A$1,0,0,COUNT($A:$A),COUNTA($1:$1)))

but this gives us 6072, not the expected 136.If you evaluate the OFFSET bit, you will see why, as it returns

{0,"Amt1","Amt2","Amt3";2005,1,2,3;2006,5,6,7;2007,9,10,11}

that is the headings are included, and it excludes the last row and last column of numbers. Because A1 is blank, COUNT($A:$A) returns 4, and COUNT($1:$1) also returns 4, so the data being summed is in the range A1:D4, not B2:E5, or even B2:E5.

It looks easily circumvented, we could add one to each COUNT to accommodate blank A1, giving us

=SUM(OFFSET($A$1,0,0,COUNT($A:$A)+1,COUNTA($1:$1)+1))

Unfortunately, this is wrong also because some of our headings are numbers, the years get added in.

Another way, by knowing that we have one row and one column of headings, is to start our range at B2, either with

=SUM(OFFSET($B$2,0,0,COUNT($A:$A),COUNTA($1:$1)))

or

=SUM(OFFSET($A$1,1,1,COUNT($A:$A),COUNTA($1:$1)))

I don't think there is any foolproof way to handle this other than understanding your data. I suppose that you could try counting the blanks in the top-left corner, but that would get complex and gets thrown by blanks in the data, where do you stop.