Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default Data Validation and Dependent Lists Q

I wish to create a Data Validation Dependent Lists if a certain Value
appears in a Cell (say A1). I have looked at Contextures and it
explains it, but what if the value selected in my cell (A1) is =
London, then I want the user to type the appropriate values into the
'Data Validation cell' instead of from a dependent list selection. If
the value selected in A1 = Paris, then I wish the dependent list to
kick in.

The reason I ask is if London is selected, product codes (i.e. the
dependent lists) run to 100's, if Paris is selected I have only 4
product codes, so I would let the user enter for London but for Paris
they would select from only 4. Just makes things easier for me than
maintaining a dynamic list of 100's

Thanks

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Data Validation and Dependent Lists Q

You only have to enter the list once. DV has to be better as the user then
cannot make a mistake, if they have to type it in they could type anything.
I certainly wouldn't want to have to enter it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message
oups.com...
I wish to create a Data Validation Dependent Lists if a certain Value
appears in a Cell (say A1). I have looked at Contextures and it
explains it, but what if the value selected in my cell (A1) is =
London, then I want the user to type the appropriate values into the
'Data Validation cell' instead of from a dependent list selection. If
the value selected in A1 = Paris, then I wish the dependent list to
kick in.

The reason I ask is if London is selected, product codes (i.e. the
dependent lists) run to 100's, if Paris is selected I have only 4
product codes, so I would let the user enter for London but for Paris
they would select from only 4. Just makes things easier for me than
maintaining a dynamic list of 100's

Thanks



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default Data Validation and Dependent Lists Q

On May 19, 12:01 pm, "Bob Phillips" wrote:
You only have to enter the list once. DV has to be better as the user then
cannot make a mistake, if they have to type it in they could type anything.
I certainly wouldn't want to have to enter it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message

oups.com...



I wish to create a Data Validation Dependent Lists if a certain Value
appears in a Cell (say A1). I have looked at Contextures and it
explains it, but what if the value selected in my cell (A1) is =
London, then I want the user to type the appropriate values into the
'Data Validation cell' instead of from a dependent list selection. If
the value selected in A1 = Paris, then I wish the dependent list to
kick in.


The reason I ask is if London is selected, product codes (i.e. the
dependent lists) run to 100's, if Paris is selected I have only 4
product codes, so I would let the user enter for London but for Paris
they would select from only 4. Just makes things easier for me than
maintaining a dynamic list of 100's


Thanks- Hide quoted text -


- Show quoted text -


Thanks Bob, not quite, products are added and deleted from one
particular list and I've 20 users working on the file at remote level,
so I couldn't physically maintain it, thus I thought if they could
manually type for one location and use drop downs for the other
(smaller) locations

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,979
Default Data Validation and Dependent Lists Q

You could name a blank cell London, and in the data validation dialog
box, choose Allow List. For the formula, enter:
=IF(C6="London",London,INDIRECT(C6))
Add a check mark to 'Ignore Blanks'
You'll be able to type anything if London is selected, and a list will
appear for other cities.
However, with Ignore Blanks selected, using INDIRECT, a user will also
be able to enter any value when a list is available.

Sean wrote:
On May 19, 12:01 pm, "Bob Phillips" wrote:

You only have to enter the list once. DV has to be better as the user then
cannot make a mistake, if they have to type it in they could type anything.
I certainly wouldn't want to have to enter it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sean" wrote in message

groups.com...




I wish to create a Data Validation Dependent Lists if a certain Value
appears in a Cell (say A1). I have looked at Contextures and it
explains it, but what if the value selected in my cell (A1) is =
London, then I want the user to type the appropriate values into the
'Data Validation cell' instead of from a dependent list selection. If
the value selected in A1 = Paris, then I wish the dependent list to
kick in.


The reason I ask is if London is selected, product codes (i.e. the
dependent lists) run to 100's, if Paris is selected I have only 4
product codes, so I would let the user enter for London but for Paris
they would select from only 4. Just makes things easier for me than
maintaining a dynamic list of 100's


Thanks- Hide quoted text -


- Show quoted text -



Thanks Bob, not quite, products are added and deleted from one
particular list and I've 20 users working on the file at remote level,
so I couldn't physically maintain it, thus I thought if they could
manually type for one location and use drop downs for the other
(smaller) locations



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default Data Validation and Dependent Lists Q

On May 19, 4:52 pm, Debra Dalgleish wrote:
You could name a blank cell London, and in the data validation dialog
box, choose Allow List. For the formula, enter:
=IF(C6="London",London,INDIRECT(C6))
Add a check mark to 'Ignore Blanks'
You'll be able to type anything if London is selected, and a list will
appear for other cities.
However, with Ignore Blanks selected, using INDIRECT, a user will also
be able to enter any value when a list is available.





Sean wrote:
On May 19, 12:01 pm, "Bob Phillips" wrote:


You only have to enter the list once. DV has to be better as the user then
cannot make a mistake, if they have to type it in they could type anything.
I certainly wouldn't want to have to enter it.


--
HTH


Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)


"Sean" wrote in message


groups.com...


I wish to create a Data Validation Dependent Lists if a certain Value
appears in a Cell (say A1). I have looked at Contextures and it
explains it, but what if the value selected in my cell (A1) is =
London, then I want the user to type the appropriate values into the
'Data Validation cell' instead of from a dependent list selection. If
the value selected in A1 = Paris, then I wish the dependent list to
kick in.


The reason I ask is if London is selected, product codes (i.e. the
dependent lists) run to 100's, if Paris is selected I have only 4
product codes, so I would let the user enter for London but for Paris
they would select from only 4. Just makes things easier for me than
maintaining a dynamic list of 100's


Thanks- Hide quoted text -


- Show quoted text -


Thanks Bob, not quite, products are added and deleted from one
particular list and I've 20 users working on the file at remote level,
so I couldn't physically maintain it, thus I thought if they could
manually type for one location and use drop downs for the other
(smaller) locations


--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -


Thanks Debra, I'm going to have a go at it

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation - Dependent Lists & Conditional Formatting Bob Excel Worksheet Functions 18 October 4th 07 03:24 AM
dependent lists and data presentation CaOrAl Excel Discussion (Misc queries) 2 February 14th 07 03:13 PM
Data Validation & Dependent Lists Bob Excel Worksheet Functions 3 December 21st 06 03:38 PM
Dependent List- Data Validation Annie Excel Worksheet Functions 2 September 23rd 05 03:40 PM
Data Validation - Create dependent lists Little pete Excel Discussion (Misc queries) 1 May 23rd 05 12:04 PM


All times are GMT +1. The time now is 03:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"