+ Reply to Thread
Results 1 to 3 of 3

Data validation formula to return list of orders based on vendor

  1. #1
    Registered User
    Join Date
    09-11-2018
    Location
    South Africa
    MS-Off Ver
    Microsoft Office 365
    Posts
    7

    Lightbulb Data validation formula to return list of orders based on vendor

    Good day,

    I am trying to setup a formula-driven data validation list that will give me the option to select order numbers based on the vendor.

    The problem I have is that the maximum limit of character-input for a formula is 256 characters, this creates a big problem in my sample, as I am using nested IF functions to call a named range of order numbers per vendor.

    The sheet has 2 tabs, TEST & ORDERS:

    On the ORDERS tab, I have a column"INDEX" that extracts all the unique order numbers from a Dynamic Table. Column"Vendor" is based on an INDEX & MATCH function that returns the vendor ID from the dynamic table.
    On the following columns, I have 2 columns for each vendor(say 40 vendors), on the 1st column of every vendor I used a formula to extract an order number if the "vendor" column matches the vendor ID, the next column then looks at the entire
    1st vendor ID column and lists the order numbers from top to bottom in order to create the validation list. I then proceed to create an OFFSET function based named range for each of the vendor IDs, so that as the rows are increased as orders numbers are updated, the list will keep growing based on each vendor. So in effect, each vendor has its own named range.

    On the TEST tab, as the orders are processed, I will need a data validation list in order to select an order number from a list in column D.
    The problem is, I used a nested IF statement for each of the vendors and the characters run out.
    Here is the actual formula I used: IF(C2="VENDOR_1",VENDOR_1_ORDERLIST,""), I then iterated for each vendor until the 256 characters were spent.

    1)Is there any alternative way to get a validation list based on named ranges for each vendor(40 for example) by not using IF statements?
    2)Is there any better way to do away with all the vendor columns and only use 1 column as a named range?

    Please see attached sample, any questions on the actual formulas used and setup is welcome
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Data validation formula to return list of orders based on vendor

    ORDERS tab:

    Column A deleted as unnecessary.

    C2: =IF($B2=C$1, N(C1)+1,N(C1)) (copied down)
    D1: =MAX(C:C) (do not copy down)
    D2: =IF(ROW(A1)>D$1,"",INDEX($A:$A,MATCH(ROW(A1),C:C,0))) (copied down)

    Now duplicate columns C:D into E:F and G:H.

    Update E1 and G1 to be VENDOR_2 and VENDOR_3.

    Your lists are now populating without array functions.



    TEST tab:

    your new Data Validation formula in D2 is:
    =OFFSET(ORDERS!$A$1,1, MATCH(C2, ORDERS!$1:$1,0), INDEX(ORDERS!$1:$1, MATCH(C2, ORDERS!$1:$1, 0)+1))

    Copy D2 down.

    You're now fully functional.
    Attached Files Attached Files
    Last edited by JBeaucaire; 03-16-2020 at 02:10 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Data validation formula to return list of orders based on vendor

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    Note: As you are new I will add it for you.Please read forum rules which you might have missed

    https://www.mrexcel.com/board/thread...endor.1127664/

+ 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: 1
    Last Post: 01-14-2018, 11:04 AM
  2. Data Validation List - Formula to Sort and Return Unique Values in List
    By beewketu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2015, 04:18 PM
  3. [SOLVED] Determining Past Due on Outside Vendor Orders
    By greggatz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2015, 06:25 PM
  4. Data Validation List based on formula result
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2015, 11:49 AM
  5. [SOLVED] Data Validation List to Return Variable/Dynamic Data based on Reference Table
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2014, 09:08 AM
  6. [SOLVED] Based on data validation list value Formula
    By raw_geek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-11-2012, 10:12 AM
  7. Replies: 3
    Last Post: 04-11-2011, 05:52 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1