Dynamic Range limit

I would like to be able to create a named range that allows the user to move the data.

Example:
I have a heading in D3 with data below it extending to D10.

I normally define a name with RefersTo: =Offset($D$3,1,0,Counta($D:$D)-1,1)

But when the user moves the range D3:D10 to E3:E10 the named range becomes RefersTo:= Offset($E$3,1,0,Counta($D:$D)-1,1)

Hi,

How about

Please Login or Register  to view this content.

change the 1000 row ref as necessary.

Rgds

Hi Richard;

Doesn't work.

When the user moves the range D3:D10 to E3:E10 the name then ReferTo: = Offset($E$3,1,0,Counta($D4:$D1000),1)

Hi,

OK how about..
1. Name the cell D4 say 'top'
2. Now create the dynamic range name with..

Please Login or Register  to view this content.

Rgds

Hi Richard;

Good idea. Can't say I'm thrilled with the idea of having to create 2 names for the same location, but it does sound like it would work. I think it's about time I created a macro that will create the names for me.

Thanks