Adding entries to a data validation list - how to do without renaming?

I 've run into this a few times now, so thought there must be a better
way.

In XL2K, when I create a data validation list and then name the region
(I think it's called region), then when I go back to continue working
on the spreadsheet, I often realize I've missed a value or two and try
to add.

This never works with the same name, so I have to rename the list and
go back to the data validation information and put the new name of the
values and edit the cells one by one. I know there must be a better
way, but I haven't figured one out.

Thanks.

You should be able to add extra rows to a name with no problem.

Go to the name (Insert>Name>Define name ...), select the name from the list,
then click in the Refersto box. Hit the edit (F2) key, and just amend the
refersto range, then click the Add button. OK out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> I 've run into this a few times now, so thought there must be a better
> way.
>
> In XL2K, when I create a data validation list and then name the region
> (I think it's called region), then when I go back to continue working
> on the spreadsheet, I often realize I've missed a value or two and try
> to add.
>
> This never works with the same name, so I have to rename the list and
> go back to the data validation information and put the new name of the
> values and edit the cells one by one. I know there must be a better
> way, but I haven't figured one out.
>
> Thanks.
>

I don't know exactly what the problem is but there should not be one. Do
this. Say your list is in A1:A10. Select A1:A10. Find the Name box. It
is immediately above the "A" of Column A and displays the cell address of
the active cell. Click in the Name box. Type in "Region" (your range name)
without the quotes. Hit enter. That range is now named Region.
Now if you have to change the range, just do so. Then select the new
range and repeat what you did before. Use the same name. HTH Otto
"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
>I 've run into this a few times now, so thought there must be a better
> way.
>
> In XL2K, when I create a data validation list and then name the region
> (I think it's called region), then when I go back to continue working
> on the spreadsheet, I often realize I've missed a value or two and try
> to add.
>
> This never works with the same name, so I have to rename the list and
> go back to the data validation information and put the new name of the
> values and edit the cells one by one. I know there must be a better
> way, but I haven't figured one out.
>
> Thanks.
>

Insert new items in the centre of the named range, and it will
automatically expand.

Or, if the list can grow, you can use a dynamic name for the range.
There are instructions here:

http://www.contextures.com/xlNames01.html


StargateFan wrote:
> I 've run into this a few times now, so thought there must be a better
> way.
>
> In XL2K, when I create a data validation list and then name the region
> (I think it's called region), then when I go back to continue working
> on the spreadsheet, I often realize I've missed a value or two and try
> to add.
>
> This never works with the same name, so I have to rename the list and
> go back to the data validation information and put the new name of the
> values and edit the cells one by one. I know there must be a better
> way, but I haven't figured one out.
>
> Thanks.
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Otto

AFAIK you can't use the same name again using this method (well it never
works for me anyway!).

Cheers
JulieD

"Otto Moehrbach" <[email protected]> wrote in message
news:Oc%[email protected]...
>I don't know exactly what the problem is but there should not be one. Do
>this. Say your list is in A1:A10. Select A1:A10. Find the Name box. It
>is immediately above the "A" of Column A and displays the cell address of
>the active cell. Click in the Name box. Type in "Region" (your range
>name) without the quotes. Hit enter. That range is now named Region.
> Now if you have to change the range, just do so. Then select the new
> range and repeat what you did before. Use the same name. HTH Otto
> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> news:[email protected]...
>>I 've run into this a few times now, so thought there must be a better
>> way.
>>
>> In XL2K, when I create a data validation list and then name the region
>> (I think it's called region), then when I go back to continue working
>> on the spreadsheet, I often realize I've missed a value or two and try
>> to add.
>>
>> This never works with the same name, so I have to rename the list and
>> go back to the data validation information and put the new name of the
>> values and edit the cells one by one. I know there must be a better
>> way, but I haven't figured one out.
>>
>> Thanks.
>>
>
>

Nor me, when you re-enter the name, it just selects the previous range.

Bob


"JulieD" <[email protected]> wrote in message
news:%[email protected]...
> Hi Otto
>
> AFAIK you can't use the same name again using this method (well it never
> works for me anyway!).
>
> Cheers
> JulieD
>
> "Otto Moehrbach" <[email protected]> wrote in message
> news:Oc%[email protected]...
> >I don't know exactly what the problem is but there should not be one. Do
> >this. Say your list is in A1:A10. Select A1:A10. Find the Name box.
It
> >is immediately above the "A" of Column A and displays the cell address of
> >the active cell. Click in the Name box. Type in "Region" (your range
> >name) without the quotes. Hit enter. That range is now named Region.
> > Now if you have to change the range, just do so. Then select the new
> > range and repeat what you did before. Use the same name. HTH Otto
> > "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> > news:[email protected]...
> >>I 've run into this a few times now, so thought there must be a better
> >> way.
> >>
> >> In XL2K, when I create a data validation list and then name the region
> >> (I think it's called region), then when I go back to continue working
> >> on the spreadsheet, I often realize I've missed a value or two and try
> >> to add.
> >>
> >> This never works with the same name, so I have to rename the list and
> >> go back to the data validation information and put the new name of the
> >> values and edit the cells one by one. I know there must be a better
> >> way, but I haven't figured one out.
> >>
> >> Thanks.
> >>
> >
> >
>
>

JulieD & Bob
Is there no end to this learning business? Thanks for that correction.
I do know, though, that the Insert - Name - Define works. Just type the
same name with the new reference. Otto
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Nor me, when you re-enter the name, it just selects the previous range.
>
> Bob
>
>
> "JulieD" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi Otto
>>
>> AFAIK you can't use the same name again using this method (well it never
>> works for me anyway!).
>>
>> Cheers
>> JulieD
>>
>> "Otto Moehrbach" <[email protected]> wrote in message
>> news:Oc%[email protected]...
>> >I don't know exactly what the problem is but there should not be one.
>> >Do
>> >this. Say your list is in A1:A10. Select A1:A10. Find the Name box.
> It
>> >is immediately above the "A" of Column A and displays the cell address
>> >of
>> >the active cell. Click in the Name box. Type in "Region" (your range
>> >name) without the quotes. Hit enter. That range is now named Region.
>> > Now if you have to change the range, just do so. Then select the
>> > new
>> > range and repeat what you did before. Use the same name. HTH Otto
>> > "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>> > news:[email protected]...
>> >>I 've run into this a few times now, so thought there must be a better
>> >> way.
>> >>
>> >> In XL2K, when I create a data validation list and then name the region
>> >> (I think it's called region), then when I go back to continue working
>> >> on the spreadsheet, I often realize I've missed a value or two and try
>> >> to add.
>> >>
>> >> This never works with the same name, so I have to rename the list and
>> >> go back to the data validation information and put the new name of the
>> >> values and edit the cells one by one. I know there must be a better
>> >> way, but I haven't figured one out.
>> >>
>> >> Thanks.
>> >>
>> >
>> >
>>
>>
>
>

On Wed, 2 Feb 2005 08:05:55 -0500, "Otto Moehrbach"
<[email protected]> wrote:

>I don't know exactly what the problem is but there should not be one. Do
>this. Say your list is in A1:A10. Select A1:A10. Find the Name box. It
>is immediately above the "A" of Column A and displays the cell address of
>the active cell. Click in the Name box. Type in "Region" (your range name)
>without the quotes. Hit enter. That range is now named Region.
> Now if you have to change the range, just do so. Then select the new
>range and repeat what you did before. Use the same name. HTH Otto

But that's just it, you select the range and go to name it but if you
use the same name, the region selected shrink back to what it was
originally. And then when I'd go back to the spreadsheet, the new
data wasn't added.

I'll try Bob Phillips' workaround as it seems that a solution other
than trying to add the info physically would work. I've run into this
before in other contracts, and therefore other machines, so this isn't
the first time this has happened to me.

Thanks.

>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>news:[email protected]...
>>I 've run into this a few times now, so thought there must be a better
>> way.
>>
>> In XL2K, when I create a data validation list and then name the region
>> (I think it's called region), then when I go back to continue working
>> on the spreadsheet, I often realize I've missed a value or two and try
>> to add.
>>
>> This never works with the same name, so I have to rename the list and
>> go back to the data validation information and put the new name of the
>> values and edit the cells one by one. I know there must be a better
>> way, but I haven't figured one out.
>>
>> Thanks.
>>
>

On Wed, 2 Feb 2005 23:25:52 +0800, "JulieD"
<[email protected]> wrote:

>Hi Otto
>
>AFAIK you can't use the same name again using this method (well it never
>works for me anyway!).

Ditto. The same name has never worked for me.
>
>Cheers
>JulieD
>
>"Otto Moehrbach" <[email protected]> wrote in message
>news:Oc%[email protected]...
>>I don't know exactly what the problem is but there should not be one. Do
>>this. Say your list is in A1:A10. Select A1:A10. Find the Name box. It
>>is immediately above the "A" of Column A and displays the cell address of
>>the active cell. Click in the Name box. Type in "Region" (your range
>>name) without the quotes. Hit enter. That range is now named Region.
>> Now if you have to change the range, just do so. Then select the new
>> range and repeat what you did before. Use the same name. HTH Otto
>> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>> news:[email protected]...
>>>I 've run into this a few times now, so thought there must be a better
>>> way.
>>>
>>> In XL2K, when I create a data validation list and then name the region
>>> (I think it's called region), then when I go back to continue working
>>> on the spreadsheet, I often realize I've missed a value or two and try
>>> to add.
>>>
>>> This never works with the same name, so I have to rename the list and
>>> go back to the data validation information and put the new name of the
>>> values and edit the cells one by one. I know there must be a better
>>> way, but I haven't figured one out.
>>>
>>> Thanks.
>>>
>>
>>
>

On Wed, 2 Feb 2005 13:33:10 -0500, "Otto Moehrbach"
<[email protected]> wrote:

>JulieD & Bob
> Is there no end to this learning business? Thanks for that correction.
>I do know, though, that the Insert - Name - Define works. Just type the
>same name with the new reference. Otto

Sounds great! Knew there'd have to be a way. I'll go try it now.

Phew, thank goodness as it was getting to be a bit of a problem. One
doesn't always have all the data handy when one is creating the range
<g>.

Cheers!

>"Bob Phillips" <[email protected]> wrote in message
>news:[email protected]...
>> Nor me, when you re-enter the name, it just selects the previous range.
>>
>> Bob
>>
>>
>> "JulieD" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Hi Otto
>>>
>>> AFAIK you can't use the same name again using this method (well it never
>>> works for me anyway!).
>>>
>>> Cheers
>>> JulieD
>>>
>>> "Otto Moehrbach" <[email protected]> wrote in message
>>> news:Oc%[email protected]...
>>> >I don't know exactly what the problem is but there should not be one.
>>> >Do
>>> >this. Say your list is in A1:A10. Select A1:A10. Find the Name box.
>> It
>>> >is immediately above the "A" of Column A and displays the cell address
>>> >of
>>> >the active cell. Click in the Name box. Type in "Region" (your range
>>> >name) without the quotes. Hit enter. That range is now named Region.
>>> > Now if you have to change the range, just do so. Then select the
>>> > new
>>> > range and repeat what you did before. Use the same name. HTH Otto
>>> > "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>>> > news:[email protected]...
>>> >>I 've run into this a few times now, so thought there must be a better
>>> >> way.
>>> >>
>>> >> In XL2K, when I create a data validation list and then name the region
>>> >> (I think it's called region), then when I go back to continue working
>>> >> on the spreadsheet, I often realize I've missed a value or two and try
>>> >> to add.
>>> >>
>>> >> This never works with the same name, so I have to rename the list and
>>> >> go back to the data validation information and put the new name of the
>>> >> values and edit the cells one by one. I know there must be a better
>>> >> way, but I haven't figured one out.
>>> >>
>>> >> Thanks.
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>