+ Reply to Thread
Results 1 to 11 of 11

How to make a dropdown list automatically fill a desired response

  1. #1
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    How to make a dropdown list automatically fill a desired response

    Hello all! I am a new user to the board, so I really appreciate anyone who takes the time to look into this. I haven't been able to get a response from any Excel Gurus, which is why I am trying this forum.

    Link to pictures of my spreadsheet to help visually represent my question: http://i.imgur.com/aGC00XA.png

    What I currently have:

    If Existing Client is selected from the first dropdown list of {Prospect, Existing Client}, a second dropdown list provides multiple options to choose from a predefined list {AA,AB,AC,BA,BB,BC,CA,CB,CC}. See Picture 1. Alternatively, if Prospect is selected, the dropdown list only allows the user to select "N/A" from the dropdown list. See Picture 2.

    What I want:

    When Prospect is selected, I want the cell to automatically fill with "N/A" so the user doesn't have to select it from the dropdown list. Currently, the user has the option to select "N/A" from the dropdown list, but I would prefer that it automatically fill.

    I'd be happy to upload my spreadsheet somewhere as well if that would help at all!

    Thanks!
    Attached Images Attached Images
    Last edited by rbentley09; 11-03-2014 at 09:54 AM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Can anyone figure this out?

    An actual workbook would be more useful.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: Can anyone figure this out?

    Haha...I am having trouble uploading the workbook. Let's see if it works this time.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: Can anyone figure this out?

    My apologies. The title has been updated to comply with the forum rules.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to make a dropdown list automatically fill a desired response

    Right-click your worksheet tab, choose View Code, then paste this in:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: How to make a dropdown list automatically fill a desired response

    I followed your directions by pasting the macro into the View Code window, closed out of View Code, and then selected Prospect, but the cells did not automatically populate "N/A". I still had the option to select "N/A" from the dropdown list, but I need the cell to automatically populate N/A so the user doesn't have to select it from the dropdown list. Am I doing something incorrectly? Could you send me the revised worksheet that makes the two cells automatically populate "N/A" when you select Prospect from the first dropdown?

  7. #7
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: How to make a dropdown list automatically fill a desired response

    Nevermind. My macros were turned off! Thank you so much!!!

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to make a dropdown list automatically fill a desired response

    Glad to help.

  9. #9
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: How to make a dropdown list automatically fill a desired response

    romperstomper,

    This really was a great fix. One last question. Let's say I wanted to replicate your code on another part of my worksheet. I figured I would just Insert a new Class Module, Paste in your code, change "F6" to the cell I want to base my scenario off of, change "prospect" to my scenario keyword, and change the range for "N/A" values and "ClearContents" values. Unfortunately, I must be missing a step.

    Any ideas?

    Thanks!

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to make a dropdown list automatically fill a desired response

    Same worksheet, or a different one? If it's a different one, you'll need to put the similar code into the module for that sheet; if it's the same sheet, you need to modify the existing routine to check the additional cell(s) for changes.

  11. #11
    Registered User
    Join Date
    11-03-2014
    Location
    London, UK
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: How to make a dropdown list automatically fill a desired response

    It's in the same worksheet (see attachment). I now want F10 to prefill with N/A if $F$9 selection is "Long Term Loan" or give the normal dropdown options for all other options selected. Basically the same question as before, but I don't know how to edit the code. This was my best attempt. Could you please correct it?

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo err_handle
    If Not Intersect(Target, Range("F6")) Is Nothing Then
    Application.EnableEvents = False
    If LCase$(Trim$(Range("F6").Value)) = "prospect" Then
    Range("F7:F8").Value = "N/A"
    Else
    Range("F7:F8").ClearContents
    End If
    If LCase$(Trim$(Range("F9").Value)) = "Long Term Loan" Then
    Range("F10:F10").Value = "N/A"
    Else
    Range("F10:F10").ClearContents
    End If
    End If

    clean_up:
    Application.EnableEvents = True
    Exit Sub

    err_handle:
    Resume clean_up
    End Sub

    Thanks again!
    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)

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2014, 08:05 AM
  2. Replies: 1
    Last Post: 02-18-2013, 06:09 AM
  3. Sum formula to exclude monthly budget figure when actual figure is entered
    By rocketmail in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2013, 04:22 AM
  4. Cross Reference to a Figure actually showing figure, not just caption
    By mgaworecki in forum Word Formatting & General
    Replies: 2
    Last Post: 02-23-2012, 10:53 AM
  5. Calculating monthly sales figure required to make annual turnover figure
    By CatIsoSio Sky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-27-2010, 04:42 PM

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