Excel - Offset Range help 2

[srogers]

This is what I'm using to define my range:

=OFFSET(GL_Data!$A$1,0,0,COUNTA(GL_Data!$A:$A),COUNTA(GL_Data!$1:$1))

My question is:
Can I leave a couple of blank columns and then use some columns to the right of that?
At some point when I am adding these columns I check the data range and it is jumping over the blank columns to include the others I added.

The above formula defines columns A thru F. I put some formulas starting in I and now I see the range is including over to column K. I'm not sure why it is stopping there because I used thru column L.

Does it have anything to do with row 1? Can I still use some columns if I leave row 1 blank?

Thanks for your help.

 

[GlennUK]

Yes it has everything to do with row 1.

The COUNTA function counts non-blank cells in the entire row to make the range column width. You have left a column or two blank and then started entering stuff in that row again ..... well that's really going to mess the automated range creation, which is why it's pointing across to column K ( having counted 11 non-blank cells in row 1 ) instead of pointing to column L ( even though you really want it to point to column F ).

So, yes to the second part of your query, you can use extra columns, and not have them included in the automated range by leaving row 1 blank.

Phew, hope that explained it for you!

Cheers, Glenn.

 

[SkipVought]

Hi,

To emphasize what Glenn has already stated so well,

the last 2 arguments define the NUMBER OF ROWS and NUMBER OF COLUMNS respectively that describe your table dimensions. Since you are using the COUNTA function to calculate these values, you must use a COLUMN & ROW that ALWAYS will have a value for the full range that represents the table dimensions. That Column/Row need NOT intersect with the FIRST argument representing the START of the OFFSET reference.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[srogers]

Yes, thank you - that explained it. I've looked at my worksheet and what you've said and I'm making the changes.

Thanks so much!