dynamic name table

[white_flag]

Hello

I like to add an dynamic table range via INSERT>>NAME>>DEFINE ...than I put the name "my_table". on the reference I add this:

=OFFSET(data!$D$5;0;0;COUNTA(data!$A:$A);COUNTA(data!$A:$A))

but the table have all the time ONE extra row .. I like to delete that row from definition ..but How?

 

[Andrew Poulsom]

Like this?

=OFFSET(data!$D$5;0;0;COUNTA(data!$A:$A)-1;COUNTA(data!$A:$A))

 

[white_flag]

thank you. It is working well.
But I have another table with different rows and columns. I put the same definition and on the range is missing one column and 13 rows

entire table is 10 columns and 21 rows. Any Ideea

 

[Andrew Poulsom]

Why are you setting the number of columns to the count of the non-blank cells in column A? Shouldn't you be counting along the row?

 

[white_flag]

maybe I do not understande correct to use OFFSET

OFFSET(reference,rows, cols,height,width)

 

[white_flag]

like thsi is not going

=OFFSET(Data!$C$10;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$C:$C))

=OFFSET(Data!$C$10;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$1:$1))

 

[Andrew Poulsom]

For width you have the same as height. You need something like:

COUNTA($5:$5)

 

[white_flag]

=OFFSET(Data!$C$10;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$5:$5))

not going

 

[Andrew Poulsom]

What range do you expect to be set?

You need to count the same column and row as OFFSET's first argument (adjusting for non-blank cells above or to the left).