data validation on other sheet

[Pierre via OfficeKB.com]

Hi,

If i use datavalidation and use a list to choose from, i can only make a list
on the same sheet where the cells are with data validation.
how can i have a list to choose from that is on another sheet ?
Thanks,
Pierre

 

[Tom Ogilvy]

Insert => Name => Define

Name: List1
RefersTo: =Sheet3!$A$1:$A$20

Click the Add Button

then in data validation under source put in

=List1

 

[Dave Peterson]

If you give that list on the secondary sheet a nice name, you can use it in your
data|validation.

See Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html#Name

 

[Pierre via OfficeKB.com]

Thanks Tom, works great.
Pierre

Insert => Name => Define

Name: List1
RefersTo: =Sheet3!$A$1:$A$20

Click the Add Button

then in data validation under source put in

=List1

--
Regards,
Tom Ogilvy

Hi,

[quoted text clipped - 3 lines]

Thanks,
Pierre

 

[Pierre via OfficeKB.com]

Hi Tom,

Is there also a way to do the following:

ist 2 columns (for example 'client number' and 'name') in the validation
field.
When a client is chosen, only use the client number to fill the cell with.
Thanks,
Pierre

Thanks Tom, works great.
Pierre

Insert => Name => Define

[quoted text clipped - 16 lines]

 

[Tom Ogilvy]

Not with data validation. You can have an adjacent cell that does a vlookup
on the client name, or if it is on the right of the client number, you can
use =index(ClientNumberRange,Match(validationCell,ClientNameRange,0),1)

while waiting for a selection in the data validation cell

=if(validationcell="","",index(ClientNumberRange,Match(validationCell,Client
NameRange,0),1))

--
Regards,
Tom Ogilvy

Hi Tom,

Is there also a way to do the following:

ist 2 columns (for example 'client number' and 'name') in the validation
field.
When a client is chosen, only use the client number to fill the cell with.
Thanks,
Pierre

Thanks Tom, works great.
Pierre

Insert => Name => Define

[quoted text clipped - 16 lines]

Thanks,
Pierre