Using lists for validation

  • Thread starter Thread starter Les Isaacs
  • Start date Start date
L

Les Isaacs

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
 
Hi

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
|
|
 
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
 
Hello Niek

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

Thanks again
Les
 
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
 
Back
Top