Named Range Automatically expand

Topic

New Reply

I one saw a formula typed in the “refers to” section of the Name, Define dialog box which would make the named range expand if additonal rows/comumns were added. I believe it included Offset and Count along the way! Can anybody help please.

Thanks

Roberta

Reply | Quote

Replies

Assuming your data starts in A1 the following formula in the “refers to” text box should give you a range that grows/shrinks as you enter/delete data.

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

Using this method means you cannot use Row 1 or Column A for any other data. You might like to include a sheet name reference in the formula.

Andrew C

Reply | Quote

Thanks for this – I have tried it however I must be doing something wrong!! I have gone to “define” typed a name and entered the “incantation” into the refers to box. Though it is still there when I go back to the name dialog box it does not appear in the drop down on the toolbar or appear when I press F3 – I must be missing something vital here!

Thanks

Reply | Quote

Before you set a range like this it is best to have some data in the range, otherwise Excel cannot calculate the extent of the range. See attached worksheet, which should help. I have created a range “Incantation” which you can play around with.

Andrew

Reply | Quote

Thanks Roberta for the term “incantation” I think I’ll use it in my next advanced Excel class … should be good for a few

Reply | Quote

Thanks for this – I see the two things I was missing – use F5 to go to the range rather than expect to see it on the list and get some data in the range before you start!

Cheers

Roberta

Reply | Quote

http://www.elementkjournals.com/ime/9603/ime96031.htmThis Link may be of help.

OK I surrender how do you enter those neat This Link messages without the whole URL showing?

Reply | Quote

Check out Forum Help for further instructions.

Reply | Quote

URL Tag style 2.

Reply | Quote

Thanks for this – interesting site

Cheers

Roberta

Reply | Quote

Found them! Here are two links it has taken me a while to find again. The one on charting is especially good.

Dynamic Ranges
Charting Dynamic Data

Reply | Quote

Ooooh, nice site, Michael! You da man!

Reply | Quote