+ Reply to Thread
Results 1 to 10 of 10

How to do dependent variable drop down lists without using list names.

  1. 11-13-2015, 08:23 PM #1
    atindall
    atindall is offline
    Registered User
    Join Date
    11-13-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    6

    How to do dependent variable drop down lists without using list names.

    Hi all,

    I need help in tracking my dockets from sub-contractors. In the spreadsheet attached, i would like to be able to enter in the docket number but then select the company from a drop down list which is taken from a list in the contract items tab, this part i can do no problem. What i then require though is that the drop down list for the contract item show only the items relevant to that company as i have possibly hundreds of items with all the subcontractors. The rate is then just achieved using a Vlookup statement. is there an easy way to do this or is my set out of data wrong? i would like to keep the list for contract items as it is as it makes finding the rate and adding in new items very easy.

    thanks


    Docket tracker.xlsxDocket tracker.xlsx
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 11-13-2015, 08:41 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: How to do dependent variable drop down lists without using list names.

    Hi, welcome to the forum

    1st, make sure that your "company" matches. The Company in the DD has no space, the Company in column A has a space before the number.

    To pull out the items related to a specific company, try this ARRAY formula in C2, copied down...
    =IFERROR(INDEX('Contract items'!$B:$B,SMALL(IF('Contract items'!$A$2:$A$13=$B$2,ROW('Contract items'!$A$2:$A$13)),ROWS($A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    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
    Reply With QuoteRegister To Reply
  3. 11-13-2015, 08:52 PM #3
    atindall
    atindall is offline
    Registered User
    Join Date
    11-13-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: How to do dependent variable drop down lists without using list names.

    Ahh excellent one more thing, how do i change this so that C2 becomes a data validation drop down list with those specific contract items for that specific company.
    Reply With QuoteRegister To Reply
  4. 11-13-2015, 09:10 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: How to do dependent variable drop down lists without using list names.

    Create a range name (I called it ContractItem) and in the Referes To, use this...
    =OFFSET('Data input'!$C$2,0,0,COUNTIF('Data input'!C:C,">""")-1,1)

    Then create your DD and use =ContractItem
    Reply With QuoteRegister To Reply
  5. 11-13-2015, 09:22 PM #5
    atindall
    atindall is offline
    Registered User
    Join Date
    11-13-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: How to do dependent variable drop down lists without using list names.

    hmmm struggling any chance you could send it back to us?
    Reply With QuoteRegister To Reply
  6. 11-13-2015, 09:27 PM #6
    atindall
    atindall is offline
    Registered User
    Join Date
    11-13-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: How to do dependent variable drop down lists without using list names.

    it only returns one item and that is the rate and not a selection of contract items aka Item 1, Item 2, etc.
    Reply With QuoteRegister To Reply
  7. 11-13-2015, 09:32 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: How to do dependent variable drop down lists without using list names.

    Just sending the file may not really help much, if you dont know how to do it

    1. right-click on any cell and select Define Name
    2. in Name, enter this...
    ContractItem
    3. in Refers To, enter this...
    =OFFSET('Data input'!$C$2,0,0,COUNTIF('Data input'!C:C,">""")-1,1)

    You now have a dynamic named range.

    4. Wherever you want the Iten DD, enter...
    - click data tab/data Validation
    - under Allow, select List
    - in Source enter...
    =ContractItem

    If that still doesnt help, explain which part you are struggling with
    Reply With QuoteRegister To Reply
  8. 11-13-2015, 09:45 PM #8
    atindall
    atindall is offline
    Registered User
    Join Date
    11-13-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: How to do dependent variable drop down lists without using list names.

    this is true.

    i think im struggling with the data drop down. In cell C2 i would like it to be able to choose from one of the 4 contract items that Company 1 has. upon selecting this the rate will automatically appear (i am ok with rate part).
    Reply With QuoteRegister To Reply
  9. 11-13-2015, 09:53 PM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: How to do dependent variable drop down lists without using list names.

    hmmm OK, you cannot have that ARRAY formula in C2 AND still use it as a Dropdown

    You will need to put the ARRAY formula into a helper somewhere, then reference that with the DD
    Reply With QuoteRegister To Reply
  10. 11-13-2015, 09:59 PM #10
    atindall
    atindall is offline
    Registered User
    Join Date
    11-13-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    6

    Re: How to do dependent variable drop down lists without using list names.

    thanks will have try
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Two different lists with the same name range for dependent drop-down lists
    By MMeGa in forum Excel General
    Replies: 11
    Last Post: 10-08-2021, 04:55 AM
  2. “Data Validation List” How Do You ADD 3 Lists Of Names, Into 1 Drop Down list?
    By StickCity in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2015, 01:40 PM
  3. Dependent Drop Down Lists: Combining Dropdown list and free format text options
    By JLD1616 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2014, 02:11 PM
  4. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  5. [SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists
    By betic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2012, 03:47 PM
  6. Variable Drop Down Dependent List with Multiple Criteria
    By Spellbound in forum Excel Formulas & Functions
    Replies: 43
    Last Post: 02-02-2012, 06:40 AM
  7. Dependent variable in Drop-Down List
    By excelhelp18 in forum Excel General
    Replies: 1
    Last Post: 03-30-2009, 06:58 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1