+ Reply to Thread
Results 1 to 4 of 4

Data validation: How to create different drop down lists depending on user's response?

  1. #1
    Registered User
    Join Date
    01-30-2012
    Location
    cairns
    MS-Off Ver
    Excel 2003
    Posts
    29

    Data validation: How to create different drop down lists depending on user's response?

    I have a spreadsheet attached 'Dose report1', done on Excel 2003, that transfers data from sheet1 to sheet2 once the user presses a Command button. Works but I need to add some functions for it to be usable.

    These functions are all relating to:
    1. different drop down lists appearing depending on which option the user selects in the previous cell.
    2. once an option is selected, cells that aren't applicable to the response become greyed out and 'unclickable' - and it must be obvious so that they don't continue to click/type where they shouldn't - a good idea would be to set up that if you're tabbing across cells, you skip straight over it + make the cell grey or something.

    What's needed is explained below:

    1. clicking the 'Machine' drop down list E:14 and selecting 'Siemens' the following cells should be unusable (so that you can't write in it and looks something like this attachment attachment '1.'):
    - the 'Field size' cell F:14 is unusable
    - the 'DAP (mGy.m2)' cell M:14 is unusable

    2. clicking the same drop down list and selecting 'Phillips', then (attachment 2):
    - the 'DAP (µGy.m2)' located N:14 unusable

    3. the next few are related to the 'Exam type' cell H:13
    - selecting 'Orthopaedics' from the drop down list should then create a certain list in the next cell, 'Exam' I:14, shown in attachment 3 + the 'Acquisition time' cell O:14 should grey out
    - selecting 'Vascular' from the drop down list should create a different drop down list in 'Exam' I:14, shown in attachment 4 + the 'Acquisition time' cell O:14 should now be active
    - selecting 'ERCP' or 'Lap Chole' from H:14 should grey out 'Exam' cell I:14 + grey out 'Acquisition time' cell O:14
    - selecting 'Other' from H:13 should let the user enter whatever they want in I:13 'Exam' + grey out O:14 as well.

    (so: cell O:14 'Acquisition time' should be greyed out for every choice in the drop down of H:14 'Exam type' except 'Vascular')

    Is all of this possible to do?

    The code I have at the moment is:

    Please Login or Register  to view this content.
    I would really appreciate your help on this one, it's for work and I'm trying to make sure we get reliable data so ppl can't mismatch the incorrect fields....... way over my head though, any suggestions??

    Willge
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Data validation: How to create different drop down lists depending on user's response?

    Hi

    Almost all that you ask for(and are many), can do using Custom Validation and Conditional Formatting.

    I Show you the way in the example. I made 3-4 of these for you to show the way.

    Hope to helps you.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    01-30-2012
    Location
    cairns
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Data validation: How to create different drop down lists depending on user's response?

    Thanks very much for the advice!

  4. #4
    Registered User
    Join Date
    01-30-2012
    Location
    cairns
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Data validation: How to create different drop down lists depending on user's response?

    Thanks for your help so far. I can add these custom validation settings for cells that don't have drop down lists, and have done most of them as in the attached spreadsheet, but I need to add your formula...
    Please Login or Register  to view this content.
    .... together with a data validation list as in attachment 1? When "Siemens" is selected in E14 I need E15 to be unusable with this code (as well as E15 having it's own drop down list when Siemens in E14 is NOT selected).

    Can you also please explain how to add more disallowed selections into this formula above, see attachment 3. The only option from H14 that should allow input to O14 is "Vascular" - how do you add these others to custom validation of O14? I've tried a few things like inserting commas or adding others inbetween "" etc but can't get it.

    Lastly about having different drop down lists depending on the selection of another cell, how does that work? I don't really want to have lists on the spreadsheet of certain drop down list contents, can I get this organised doing something similar to what I'm doing now? Attachment 3 shows a list to go with "orthopaedics" in H14. Attachment 4 shows a list to go with "Vascular". All other options in H14 should allow the user to input whatever they want in H15. I know I'm asking three different questions here but I'd really appreciate your help on this!
    Attached Images Attached Images
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1