Edit problem for data validation drop down list

P

purpletigerface

Hi
I've created named dropdown lists for data validation which are on a separate
worksheet. They worked fine until I tried to add to them. For example I have
a list named 'Years', which I have added a few years to. I can't seem to
redefine the name 'Years' to include the extra items. I've tried highlighting
the whole list and renaming it 'Years' but it just reverts to the old section
of the list. I can't change the source range in the Data - Validation box, as
this shows the list name, not the souce range. Anyone know how I do this?
Thanks
Karen
 
B

Bob Phillips

You need to go into the Names dialog (Ctrl-F3) and extend the refersto
value.

Why not use a dynamic range, then you never change it again

=OFFSET($M$1,,,COUNT($M:$M),1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
P

purpletigerface

Thanks Bob
It's all sorted now.
Karen

Bob said:
You need to go into the Names dialog (Ctrl-F3) and extend the refersto
value.

Why not use a dynamic range, then you never change it again

=OFFSET($M$1,,,COUNT($M:$M),1)
Hi
I've created named dropdown lists for data validation which are on a
[quoted text clipped - 11 lines]
Thanks
Karen
 
G

Guest

Here's what I do..

Go to the place where the list is on the worksheet. Click on a cell in the
list that is NOT the first one. Right click and select 'Insert...'. Select
'Shift cells Down' and click ok. Type the new data in the inserted cell.

If you want the new data as the last item, then do exactly the same as above
then right click and COPY the last cell and Paste into the inserted cell.
Then overwrite the contents of the last cell with your new data.

If you want to see exactly how the list is defined then from the main menu
go to: insert -> Name -> Define. Click on the name and at the bottom is the
list definition.

John S.

P.S. Additional 'gottcha' that can happen if the named list is on a seperate
worksheet... If your dropdown lists stop working, then the list may have
become 'local' instead of 'global' You can see that this has happened by
looking at the named list defintion. Do this: Go to on the main menus:
Insert->Name->Define. Click on the list name. if you see a sheet name to the
right of the list name, then the list is local to that worksheet and won't be
seen by the other worksheets. Do this for the worksheet with the list and the
worksheets that contain the cell validation that refers to the list.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top