+ Reply to Thread
Results 1 to 11 of 11

Drop Down List - Hide invalid options & Auto-fill if only one option

  1. #1
    Registered User
    Join Date
    05-20-2015
    Location
    London, England
    MS-Off Ver
    2010 & 2013
    Posts
    5

    Drop Down List - Hide invalid options & Auto-fill if only one option

    Hi,

    I am trying to build a quotation program using excel, but am struggling to make this part work.

    Using Data Validation, I have a column of drop down lists that let me pick an option from each category. This works fine, although I believe I need to keep the Options sorted by category for it to work (this limitation is OK). The Yellow cells in the attached sheet are the ones with drop down lists.

    The choices in the drop down then return the price of that option in the next column along, according to the model selected above. These prices differ by model, and can be Zero, or any positive or negative number. The program then sums these prices to get the total price.

    1) I am trying to get the drop down list to only show me options that are available for that model (the model is selected first). At the moment, options which are not available for that model have a price listed as "NA", but I can change this to anything as required to make the program work. (example - at the moment, selecting the drop down for category AC shows 4 options, but with Model2, there is only one valid option)

    2) I am also trying to get the drop down boxes to auto-populate with the option name if there is only one option valid for that model (as an example in Model2, Category AC, only "Option G" is possible, and I would like this to auto populate when the model is selected.

    Although the sample shows only 2 models and 10 options, the real data could be up to 50 models and 1000 options or so, and needs to be relatively straightforward to update, maintain and add to, so am trying to avoid solutions that rely on individual exceptions.

    Any help would be very much appreciated - thank you for taking the time to read this!
    Attached Files Attached Files
    Last edited by bob_g; 06-09-2015 at 05:57 AM. Reason: Question Solved

  2. #2
    Registered User
    Join Date
    04-30-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    What about this?

    You manage the data validation lists in the "DV" worksheet. For every new model, just add a new model in column A and then add another column for that model with the options. Don't leave any blank columns. The data validation will dynamically expand and contract based on the number of records (options) you havefor each model.

    Note that I built in no logic to predicate the option selection to be based on the category (because you don't have those as dropdowns currently anyways). However, it could be possible if you need that as well.

    One more note... when you change the model selection, your options will not be cleared out... so a user could end up with an invalid option selection according to the selected model. Some VBA code could be written to clear the option cells when the model selection is changed... but I am not sure if VBA is an option for you or not.
    Attached Files Attached Files

  3. #3
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    To solve the problem build an auxiliary table (M2:Q12) with the following array formula:
    For the first column of the table (M3:M12)
    Please Login or Register  to view this content.
    For other columns, the first line (N3:Q3) is
    Please Login or Register  to view this content.
    and the formula can be extended to (N4:Q12)
    Data validation has become
    Please Login or Register  to view this content.
    I attached the file
    Attached Files Attached Files
    Last edited by Josť Augusto; 05-28-2015 at 07:13 AM.

  4. #4
    Registered User
    Join Date
    05-20-2015
    Location
    London, England
    MS-Off Ver
    2010 & 2013
    Posts
    5

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Wow! Thanks Mick & Josť for such quick replies - very much appreciated!

    mick0005 - That looks promising, but I must admit I don't quite understand how I would restrict the options to the category they belong to. Each line is pre-populated with a different category, and the user has to select one option for each category - there are about 40-50 categories (only three shown in the sample sheet). Each option is only valid in one category - For example, in Category AA, one of Option A, B or C must be selected, but no others are valid, and option A,B and C can only be used within Category AA. I should have named the options more logically in the example - i.e. Option AA1, AA2, AA3, AB1, AB2, etc.


    Josť Augusto - This looks a bit more automated and looks to work well. The only part I need to figure out is how to make it look up the data for the model selected in C2 0 at the moment it is a fixed reference to Model2. I will have a play and report back shortly

    Thanks again to both of you for the help - You guys are brilliant!!

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Formula for M column[for the first column of the table (M3:M12)]
    Please Login or Register  to view this content.
    Thus dropbox is dependent of Select Model in C2.
    Note: Table1[#Headers]is the same of J2:K2

    Thanks for the compliment
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-30-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Quote Originally Posted by bob_g View Post
    Wow! Thanks Mick & Josť for such quick replies - very much appreciated!

    mick0005 - That looks promising, but I must admit I don't quite understand how I would restrict the options to the category they belong to. Each line is pre-populated with a different category, and the user has to select one option for each category - there are about 40-50 categories (only three shown in the sample sheet). Each option is only valid in one category - For example, in Category AA, one of Option A, B or C must be selected, but no others are valid, and option A,B and C can only be used within Category AA. I should have named the options more logically in the example - i.e. Option AA1, AA2, AA3, AB1, AB2, etc.


    Josť Augusto - This looks a bit more automated and looks to work well. The only part I need to figure out is how to make it look up the data for the model selected in C2 0 at the moment it is a fixed reference to Model2. I will have a play and report back shortly

    Thanks again to both of you for the help - You guys are brilliant!!
    In your original post you didn't say that Category was also a dependency and that options would be predicated on category. In fact in your uploaded file, category wasn't even a drop down. It was hard coded, so I assumed a user typed that in, and wasn't sure whether you needed this to drive your options.

    I like what Jose did

    Does Jose's solution solve your problem, or do you still need to be able to use the dropdowns for Options and Category?

  7. #7
    Registered User
    Join Date
    05-20-2015
    Location
    London, England
    MS-Off Ver
    2010 & 2013
    Posts
    5

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Thanks again to both of you - sorry for the delay in replying, Have been away at a trade show for a week.

    Jose's Solution now works brilliantly - the only part I now need to solve is for the cells to autofill if there is only one option in the drop down after selecting the model.

    In Jose's sheet, this would work as follows: If the user selects Model2 in Cell C2, then only one option is possible for Category AA (Option A). Instead of the user having to open the drop down menu and select this option themselves, it would autopopulate Cell C5 with "Option A".

    I have seen a method that involves writing an "IF" statement for every possible option, but this would be very difficult to maintain for several hundred options. The reason for it is to make selections much faster - selecting the model will generally leave only one option for about half the categories.

    Apologies about the categories in the original post. For clarity, the categories are hard coded in the real sheet, and the user has to select one option for each one. In some cases there may only be one option in that category, and in others there could be many.

    Thanks again to both of you for your help with this - I really appreciate your time and effort! Being new to the forum, is there a system for saying thanks / letting others know how good you are?

  8. #8
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Thank you for the compliments.

    To thanks you thank clicking the [* Add reputation] just below the contributer.
    Even more. You can, selecting the Advanced option, mark as resolved the issue, if applicable.

  9. #9
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Thank you for the compliments.

    To thanks you can click the [* Add reputation] just below the contributer.
    Even more. You can, selecting the Advanced option, mark as resolved the issue, if applicable.

  10. #10
    Registered User
    Join Date
    05-20-2015
    Location
    London, England
    MS-Off Ver
    2010 & 2013
    Posts
    5

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    Thanks Jose

    I still need to sort the auto-fill part, which I think might be best done by checking if there is only one valid row in the Auxiliary table for that Category, and if this is the case, making this the value in the cell, else leaving it blank and letting the user choose the correct one from the drop down list.

    Effectively, for cell C5 - albeit in text, not Excel!:

    =IF(Number of rows in Auxliary table where N3:N50000 matches B5 = 1, then value is that from Column O, same row, else blank)

    I will also need to make sure that the formula doesn't keep resetting cells back to being blank - when a drop down menu is used to select an option, this could overwrite the formula with the new data. The User can use a fresh copy of the sheet each time they wish to do a quote, or I could possibly use a macro to put the formula back where it should be in Column C each time a new model is selected?

    Thanks again for the help, from everyone who is reading and thinking of having a go.

  11. #11
    Registered User
    Join Date
    05-20-2015
    Location
    London, England
    MS-Off Ver
    2010 & 2013
    Posts
    5

    Re: Drop Down List - Hide invalid options & Auto-fill if only one option

    I've sorted it - it's not perfect, as if the user presses delete when in the cell, it becomes broken, but it works. We will set the file as read only, and the intention is the user will have to re-open the file each time they want to use it, using "Save As" to store the quote if required.

    The formula I used to get the last part to work is:

    =IF(COUNTIF(N5:N14,B7)=1,INDEX(O5:O14,MATCH(B7,N5:N14,0)),"")

    Where N5:N14 is the data in the category column within the auxiliary generated table for that model, B7 is the Category in that row, and O5:O14 is the Option Description column in the Auxlilairy table.

    Thanks again to both Jose and Mick!!

    No doubt there will be further questions as the project progresses, but for now I have a smug grin

+ 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: 2
    Last Post: 03-27-2014, 06:10 PM
  2. Auto-fill Data When Selecting Option From Drop-Down List
    By patrickdjames in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-25-2013, 11:49 AM
  3. Excel 2007 : Auto fill from a drop down list
    By ciapul12 in forum Excel General
    Replies: 0
    Last Post: 08-31-2011, 04:09 PM
  4. Auto-fill with a Drop down list
    By concretetsunami in forum Excel General
    Replies: 3
    Last Post: 09-10-2008, 12:39 PM
  5. Drop Down List & Auto Fill
    By smrsunboy in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-03-2007, 12:47 AM

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