Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Learning drop-down lists in Excel

    Hi,

    I'm at a new job and while I am experienced with excel somewhat, I've never used drop-down lists. I'm a fast learner and I need to learn this quickly as I was asked to work on one. Can someone direct me to a tutorial or briefly explain to me how they work? Thank you!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maureen,

    Enter: Excel +"drop down" +tutorial into a Google Search box and you'll get plenty of choices.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    4
    Thanked 0 Times in 0 Posts
    This particular worksheet changes everytime a different item is selected from the drop-down list. That is what I don't know how to do. There is another page in the workbook with a tab that says "do not touch" with formulas on it that correspond to the drop down list. Have you seen this before.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maureen,

    Does the Drop-down list change or just the other data on the sheet it is located on?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Sounds to me like there is a Macro running as a result of the change in the drop down.
    Also is the drop down Data Validation, A Form Drop Down Box or an Active X Control?
    You probably are not allowed to upload it, but really the only way to be able to provide guidance is to see the Workbook in question.
    Andrew

  6. #6
    New Lounger
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    4
    Thanked 0 Times in 0 Posts
    when you select a different item on the drop-down list other data on the sheet changes e.g - in column A is my dropdown list - Its a list of hospitals - in column B -which was hidden - are formulas - in a column entitled willing to work - and in the column entitled names- no formulas - are the names of social workers on my list, and then all the names in the following rows So....... when I select a particular hospital from the drop-down list it says in column A if the social worker in whatever row I select "Is Available" for that particular hospital. And I was just handed a stack of new social workers to add to this worksheet! Am I making any sense at all?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach an example file and walk us through what you want?

    Steve

  8. #8
    New Lounger
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm trying to send a copy of the worksheet but it's not working

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What do you mean by "not working"? Could you be a little more specific?
    [you may want to review http://windowssecrets.com/forums/faq...b3_attachments]

    Steve

  10. #10
    New Lounger
    Join Date
    Nov 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It sounds like filters are enabled. Are the drop-down lists on the top row?
    When you select one or more items in a filter for one column, only rows matching the filter items are displayed. Also, the items in the filter drop-downs for other columns are then reduced to only those entries still available in the remaining displayed rows.

    Filters are enabled using the "data" tab and selecting the "filters" button.

  11. #11
    New Lounger
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I tried to copy the screen but that didn't work, now I'm trying to attach my worksheet without using 'quick reply' as it instructs but it won't let me!

  12. #12
    New Lounger
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Here is my attachment........

    This is the workbook...........HELP!
    Attached Files Attached Files

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maureen,

    Actually what you have here is a Data Valadation list which currently used the Range $Y$1:$BH$1.
    Row A contains the formula: =IF(AND(SUM('Do Not Touch'!$A$5:$AJ$5)=1,BI2=1),"Willing. Check Availability","")
    You'll notice Col: BH is hidden and contains the formula: =SUM(BJ2:DS2)
    Col: BJ contains the formula: =IF(AND(Y$1=$A$1,Y2="yes"),1,0) as do cols BH through CP.
    Here's where we find the references to A1 or the data validation dropdown. Where a boolean table {Yes/No...0,1} is being generated and then summed in Col BH. Why the sum goes to DS I don't know.

    This is a very complex workbook, as this preliminary investigation shows. You're going to have to follow the formulas and trace how it all follows through. This is not going to be an easy task if you have no documentation, which I assume you don't. If I were you I'd get with your manager immediately explain this and at the same time as you are telling them how much time this is going to consume you can explain the need for good documentation for something this complex so the next time it needs to be fixed/updated the process will be very straight forward.

    Good Luck!

    Basically,
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Here's how it seems to work Maureen.

    Cell A1 is a drop down list because it is set to use "data validation", offering the list of hospitals in the top row of the sheet between Y1 and BH1

    The formulas in "do not touch" seem irrelevant as all they seem to do is confirm that the value of cell A1 is in fact a valid hospital, but A1 is validated by Excel! However, the formulas in column A of the main sheet use "do not touch" so I wouldn't don't touch it unless you know exactly why it was put there.

    Columns Y to BH contain the word "yes" if the person named on that row is prepared to work in the hospital at the top of that column. Formulas in columns BJ to CP return a 1 if the person on that row is prepared to work in the hospital selected from the drop-down list. The formulas in column BI add up the 0s and 1s in BJ to CP, effectively moving any 1s from the hospital column to column BI.

    Column A formulas just return "willing" if the selected hospital is in "do not touch" and the person in that row is willing to work there based on the value in column BI

    So, to add more cosmetologists, add their details to new rows in columns C to BH and copy the formula in A2 to col A in each of those rows and the formulas in BI2 to CP2 to cols BI to CP of those rows.

    Note that a few of the names in the list have no formula in col A. That means they will never show up as willing. Presumably because they stopped work temporarily. Personally I would have done something a little more obvious like added a further check using a column headed "active" or similar.

    I'll subscribe to this reply so you can ask more if you need to.

    Ian.

  15. #15
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Oh - sorry Retired Geek, we must have been typing our replies at the same time!

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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