Using lists for validation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Les Isaacs - 09 Dec 2007 12:54 GMT

Hello All

I have a worksheet with a cloumn where I am using validation with a list of
values on another sheet. So, for the column to be validated, in the
validation window I have "List" in the allow box, and "=clientlist" in the
source box. On the other sheet I have 6 values in column A representing the
6 client names, and I highlighted all 6 and then entered "clientlist" in the
box to the left of the = sign just below the toolbar.

This all works OK, but when I get a 7th client I can't seem to redefine the
clientlist to include it. I have tried all sorts but nothing works. I'm sure
this should be possible, but would be very grateful if someone could point
me in the right direction. Idally I would not have to redefine clientlist
every time I want to add a new record - it would be better if I could just
define the whole column (without the empty values) as the list: is this
possible?

Thanks for any help.
Leslie Isaacs

Reply to this Message

Roger Govier - 09 Dec 2007 13:56 GMT

Hi

Assuming your list of clients is on Sheet2, in column A then try
=Offset(Sheet2!$A$1,0,0,COUNTA($A:$A))

Signature

Regards
Roger Govier

> Hello All
>
[quoted text clipped - 15 lines]
> Thanks for any help.
> Leslie Isaacs

Reply to this Message

Leslie Isaacs - 10 Dec 2007 13:45 GMT

Hello Roger

Many thanks for your reply.
Before I saw it I saw Niek Otten's other suggestion, which I have now used.
I expect your suggestion will also work, but as I don't actually understand
it (I am not familiar with the Offset command, or the COUNTA command) I will
stick with Niek's suggestion (which I do understand!) - unless you can see a
problem with it?

Many thanks for your help though, it is appreciated.

Les

> Hi
>
[quoted text clipped - 20 lines]
>> Thanks for any help.
>> Leslie Isaacs

Reply to this Message

Roger Govier - 10 Dec 2007 17:56 GMT

Hi Les

No problem at all.
Niek's suggestion will work very well for you.
After doing what Niek says you might want to sort that range, so you get
names in alphabetic order

Defining Dynamic Ranges, can be very useful and there is an excellent
example shown at Debra Dalgleish's site, should you wish to try this method
at any time in the future
http://www.contextures.com/xlNames01.html#Dynamic

Signature

Regards
Roger Govier

> Hello Roger
>
[quoted text clipped - 35 lines]
>>> Thanks for any help.
>>> Leslie Isaacs

Reply to this Message

Niek Otten - 09 Dec 2007 13:56 GMT

Hi Leslie,

Insert the new names before the last one used (us the Insert>Cells or Insert>Rows command)

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello All
|
[quoted text clipped - 15 lines]
| Thanks for any help.
| Leslie Isaacs

Reply to this Message

Leslie Isaacs - 10 Dec 2007 13:46 GMT

Hello Niek

Many thanks for your reply. I have done as you suggested and it works
perfectly!

Thanks again
Les

> Hi Leslie,
>
[quoted text clipped - 29 lines]
> | Thanks for any help.
> | Leslie Isaacs

Reply to this Message

Rate this thread: