+ Reply to Thread
Results 1 to 8 of 8

Linking Dropdown to populate multiple values ranges

  1. #1
    Registered User
    Join Date
    04-02-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Linking Dropdown to populate multiple values ranges

    How do I achieve the following using Dropdown/List or functions.
    The scenario is that if I choose Type=Kg then I should get result based on Kg and Vice versa
    Sample.xls file attached for reference.


    TYPE RANGE RATE
    Kg 1 - 10 $5
    11 - 50 $15
    51 - 70 $20
    >100 $50



    Lbs 2.2 - 22 $5
    21 - 55.5 $15
    75 - 110 $20
    >300 $50
    Attached Files Attached Files
    Last edited by oldbuddy; 04-02-2020 at 11:27 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Linking Dropdown to populate multiple values ranges

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Linking Dropdown to populate multiple values ranges

    You could probably use something like...
    =cell-that-contains-value*if(cell-with-KG/lbs="KG",2.2,1)

  4. #4
    Registered User
    Join Date
    04-02-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Re: Linking Dropdown to populate multiple values ranges

    DOne, as suggested. Thanks

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Linking Dropdown to populate multiple values ranges

    OK, I added 4 named ranges to your 1, 1 each for weight ranges (KgRange and LbRange), and 1 each for Charge.

    Then in each DD, I used an INDIRECT formula to combine the Kg or Lb with either Range or Charge...
    =INDIRECT($B$31&C$30)
    =INDIRECT($B$31&D$30)

    See attached

    let me know how that works for you?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-02-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Re: Linking Dropdown to populate multiple values ranges

    Thank you so much, really appreciate it!
    This has raised curiosity on Charges! Is there a way the Charges can get populated automatically?
    I have tried the lengthy "IF" function but that would only serve for either Lbs or Kgs in the same cell

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Linking Dropdown to populate multiple values ranges

    Sure, just remove the Charges DD and replace with this...
    =VLOOKUP(C31,$C$11:$D$24,2,0)

    Note that it might be better if you center-align the cell so you can see the value, otherwise it might get hidden behind the Range DD arrow

  8. #8
    Registered User
    Join Date
    04-02-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    4

    Re: Linking Dropdown to populate multiple values ranges

    Great! Thanks again.

+ 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. [SOLVED] Multiple Dropdown list does not populate data as desired.
    By HM1961 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2019, 09:42 AM
  2. Dynamically populate dropdown values based using VLOOKUP and OFFSET?
    By jakeruby in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-24-2017, 11:33 AM
  3. Use multiple levels of Dropdown to populate a worksheet
    By ikench in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2017, 01:34 PM
  4. [SOLVED] Populate in-cell dropdown list with values based on sheet name
    By mks16 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-21-2015, 09:35 AM
  5. Multiple linking dropdown list
    By jwstimac in forum Excel General
    Replies: 2
    Last Post: 04-03-2013, 09:40 PM
  6. Replies: 1
    Last Post: 03-19-2013, 07:03 PM
  7. Linking cell color to dropdown list values
    By gessie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2008, 08:05 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