Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dynamic cell range

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Dynamic cell range

Resolved · Low Priority · Version 2003

Reply Tue 17 Aug 2010, 14:55Delegate Sanjay said...

Sanjay has attended:
Excel Advanced course

Dynamic cell range

We covered data validation where I can select values from a set list. My question is where I increase the values in the list, how can be drop down menu be automatically updated to include the new input? Suppose the items on my list in data validation are from cells C2 to C10 and I now increase the list by adding a value in cell C11. How would the drop down menu get updated?

For upcoming training course dates see: Pricing & availability

Reply Wed 18 Aug 2010, 12:00Trainer Stephen said...

RE: Dynamic cell range

Hi Sanjay

Thanks for your question

You should create a named range, by clicking on Insert-Name_Define. Give it a suitable name, and then in the refers to box enter the following formula
=OFFSET($D$1,0,0,COUNTA($D:$D),1)

The offset function starts in column D, and uses the countA function to determine how many rows hold data.

Regards

Stephen

Wed 25 Aug 2010: Automatically marked as resolved.

Excel articles

· Excel: Survival Of The Fittest
· How To Get The Best Out Of Excel With The Internet
· A Brief Glimpse At Excel's What If Analyses Tools
· Excel: From The Basic To The Advanced
· Should I Use Data Bars, Colour Scales Or Icon Sets In Excel?

Please browse our web site to find out more about
basic excel training and other Microsoft training courses.

Excel tip:

Display pictures on Chart Data Point

Replacing a single chart data point bar with a picture.
Step 1: Left click on a bar. Then, wait, and do a second single click on the bar. This will select just one data point.

Step 2: Right click on the bar and select Format Data Point.

Step 3: On the fill effects tab, choose a picture. Browse for a picture for that bar. Indicate if you want it to be stretched or stacked. Repeat for each bar.

View all Excel hints and tips