Dynamic Named Range Formula Question

[Chookz]

Hey All,
I am new to dynamic named range formulas so i watched a tutorial and got the following formula to fit my data range:

=offset(Sheet2!$A$1,1,0,counta(Sheet2!$A:$A)-1,8)

However it cuts off the 1st row of cells (the headings for my pivot) and not knowing exactly what each part of the formula details, I was wondering if anyone can fix it to include all rows?
Cheers

 

[T. Valko]

Hey All,
I am new to dynamic named range formulas so i watched a tutorial and got the following formula to fit my data range:

=offset(Sheet2!$A$1,1,0,counta(Sheet2!$A:$A)-1,8)

However it cuts off the 1st row of cells (the headings for my pivot) and not knowing exactly what each part of the formula details, I was wondering if anyone can fix it to include all rows?
Cheers

Do you want the column headers to be included in the range?

 

[Chookz]

Yes thanks, so that the headers are included when i enter this range into a pivot table. Im just not sure what part of the formula needs changing

 

[T. Valko]

Yes thanks, so that the headers are included when i enter this range into a pivot table. Im just not sure what part of the formula needs changing

Try this...

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

Here's what it means...

OFFSET starting from cell Sheet2!$A$1
By 0 rows
By 0 columns
The height of the range in rows will be the result of the COUNTA function
The width of the range is 8 columns

 

[markmzz]

Chookz,

Try this:

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

Markmzz

 

[Chookz]

Thanks guys, worked a treat. Also thanks for the explanation T.Valko!

 

[T. Valko]

Thanks guys, worked a treat. Also thanks for the explanation T.Valko!

You're welcome. Thanks for the feedback!

 

[snoopyhr]

.

 

[T. Valko]

Just an alternative to offset:
The offset function is volatile, meaning it recalculates everytime the sheet changes, causing the slowdown in calculation.

Not necessarily.

Only if there are MANY volatile functions.

Many can be anywhere from 100's to 1000's. It just depends.

Don't be affraid of volatile functions!

 

[snoopyhr]

Tried the Jeanie and did not work, so here is the short version :

As an alternative and to minimize usage of volatile offset function:
Use cell reference with combination of INDEX to create a range
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
and use it as the range name