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

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)



"purpletigerface" <u31994@uwe wrote in message news:6e3e03fa75aee@uwe...
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

Thanks Bob
It's all sorted now.
Karen

Bob Phillips wrote:
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

Delete the name "Years" then re-build it.

When re-building, it may be best to make it a dynamic range.

See Debra Dalgleish's site for this.

http://www.contextures.on.ca/xlNames01.html#Dynamic

You may also check out more on DV lists at her ExcelTips page.

http://www.contextures.on.ca/tiptech.html


Gord Dibben MS Excel MVP

On Fri, 23 Feb 2007 16:16:59 GMT, "purpletigerface" <u31994@uwe wrote:

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

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.

"purpletigerface" wrote:

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