+ Reply to Thread
Results 1 to 6 of 6

Populate data based on several drop down selections

  1. #1
    Registered User
    Join Date
    10-25-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2013
    Posts
    9

    Populate data based on several drop down selections

    I know there is probably an answer out there already. However, I do not have the time to search. But if there is already a post out there and it answers this, please tell me! Thanks! I'll describe what I want and I have attached a basic worksheet. The workbook I am building is several sheets and this will be a hidden, non-editable sheet and I can just reference the cell with the info in the hidden sheet for my working sheet. Hope that makes sense.

    I want to have a couple drop down menus using Data Validation to populate permit cost for a specific county. There are several counties and each county varies price wise, but the types of permits stay the same. I would like it to read something like this: "if [drop down cell] equals [county] and if [drop down cell] equals [type of permit], then return mark up cost for said feeder in said county. I'm not sure if and "IF" statement is what I should use, or vlookup or something. Just don't know how to formulate it. I saw some info on tables, but have never used them before, and not sure how to fit in 10+ tables into a formula. Any info is appreciated! I'm still learning everyday, so please be gentle
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Populate data based on several drop down selections

    Hi,

    I have attached your spreadsheet containing the formula that (I think) you are asking for.

    The yellow cell (A1) is the formula, the blue cell (B9) is the County, the orange cells (A11:A23) are the permit types and the green cells (B11:C23) are a named range, which have the same name as the County in the blue cell.

    Every new county that you add to the spreadsheet will need to have its own named range with the county name also for this formula to work.

    Give it a try and let me know how you go. If this doesn't work, I will need a spreadsheet with more Countys so that I can devise a formula that will work for your specific data layout.

    Hope this helps
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-25-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Populate data based on several drop down selections

    ajryan! You are a GENIUS!!! This works amazingly! When I saw how you did it, I'm so excited, I seriously nerded out!!! Was hoping you could incorporate one more drop down if possible. I've never used INDIRECT, INDEX, or MATCH, so I'm a little leery of adding anything to it without knowing where it should go. I attached the worksheet with 1 more county. I'd like to add circuits (if you saw, it was 1-30 circuits) and their corresponding prices with mark up. So, when the county and type of permit is selected, you can also select how many additional circuits to be added to total cost of permit. I colored BENTON green, and CLACKAMAS blue.

    Sorry edited the worksheet again with the drop down *circuits*. Thanks a gazillion!
    Attached Files Attached Files
    Last edited by NerdALRT; 02-06-2014 at 01:54 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Populate data based on several drop down selections

    Hahaha no problems, thanks

    Here is the new formula that I hope you were looking for.

    The new formula (A1 again) simply adds the additional cost to the original formula. As a result, I have changed your circuit named ranges by offsetting them down 1 row from where they originally were, so that if "Circuits?" is selected there is no error, and if the first option was chosen (no additional circuits I think), then the extra $94-ish wasn't added again. This was a better option for me than complicating the formula with a whole heap of error checking.

    Let me know if I've stuffed something up
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Populate data based on several drop down selections

    Hi again,

    If this is solved, could you please mark the thread as solved and click on the * next to my post to say thanks

  6. #6
    Registered User
    Join Date
    10-25-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Populate data based on several drop down selections

    I apologize for the delay here. I am just now getting to this. I'll make sure to mark this thread once solved. Thank you!

  7. #7
    Registered User
    Join Date
    10-25-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Populate data based on several drop down selections

    Worked like a charm! Thank you so much for your patience and knowledge to help me work through this!

+ 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. Populate cell data based on data validation (drop down list)
    By ish_baho in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-05-2014, 11:47 PM
  2. Need help creating a formula that will sort and populate based off certain selections
    By stephanieisanerd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 05:37 PM
  3. Replies: 17
    Last Post: 02-05-2013, 08:20 PM
  4. Filter data in one tab based on drop down selections in a different tab
    By ragonef in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2012, 09:27 AM
  5. Populate Data (Based on Drop Down Selection)
    By jkoste03 in forum Excel General
    Replies: 5
    Last Post: 07-06-2010, 03:56 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