Dynamic Named Range

[NorthbyNorthwest]

Hi, everyone. Hope someone can help me with a dynamic named range. I have a range where first data cell is B8 (row above is header), last column is DZ (column 130). I need a dynamic named range which takes into account expanding rows. I tried the following formula =OFFSET(Details!$B$8,0,0,COUNTA(Details!$B:$B),129). It doesn't do anything. No marching ants. What am I doing wrong?

 

[Anthony47]

How did you determine it doesn't work?
Select sheet Details; tab Formulas, group DefinedNames, open NameManager; select "your" Name, click once onto the "Referred to" formula: is a dotted frame displayed? How is that area delimited with the dotted frame, with respect to the area that your Name you expect to be referring?

If on a free area and using Office 365 you type the formula =YourName what do you get?

 

[NorthbyNorthwest]

OK, I followed your instructions and got it working. But now it picks up three extra rows. My data stops at row 115. The dynamic range ends at row 118. Do you why this would happen? How do I write formula to exclude these rows?

 

[Anthony47]

Try using

[B]=OFFSET(Details!$B$8,0,0,COUNTA(Details!$B8:$B200),129)[/B]

 

[NorthbyNorthwest]

Thanks for the help, Anthony. I figured out where the three extra rows came from. Appreciate all you do.

 

[Anthony47]

Thank you for the feedback