Data validation formula to return list of orders based on vendor

Cyberpunk001

New Member
Joined
Aug 27, 2018
Messages
13
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
smile.gif
 

Attachments

  • orders tab.JPG
    orders tab.JPG
    110.9 KB · Views: 8
  • test tab.JPG
    test tab.JPG
    94.4 KB · Views: 8

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
781
Office Version
  1. 365
Platform
  1. Windows
Hey, do you have the UNIQUE, FILTER, SORT functions in your Excel? (O365 functions)
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
781
Office Version
  1. 365
Platform
  1. Windows
Book1
BCDEFGHI
1CODEVENDORORDER NOUnique Vendors:VENDOR_1VENDOR_3VENDOR_2
2CODE_1VENDOR_1111111333333555555
3CODE_1VENDOR_1222222202020777777
4CODE_1VENDOR_2444444303030888888
5CODE_1VENDOR_2666666 999999
6CODE_1VENDOR_2  101010
7CODE_1VENDOR_3   
8CODE_1VENDOR_3   
9CODE_2VENDOR_4   
10CODE_2VENDOR_5   
11CODE_2VENDOR_6   
12CODE_2VENDOR_7   
13CODE_3VENDOR_8   
14CODE_3VENDOR_9   
15CODE_3VENDOR_10   
16CODE_3VENDOR_11   
17CODE_3VENDOR_35   
18VODE_X++NVENDOR_X++N   
Test
Cell Formulas
RangeFormula
G1:I1G1=INDEX(Orders!$C$2:$C$13,MATCH(0,COUNTIF(Test!$F$1:Test!F1,Orders!$C$2:$C$13),FALSE))
G2:I18G2=IFERROR(INDEX(Orders!$B$2:$B$13,AGGREGATE(15,6,(Test!G$1=Orders!$C$2:$C$13)/(Test!G$1=Orders!$C$2:$C$13)*ROW(Orders!$B$2:$B$13)-ROW(Orders!$B$1),ROWS($A$1:A1))),"")
Cells with Data Validation
CellAllowCriteria
D2:D18List=OFFSET($G$1,1,MATCH($C2,$G$1:$I$1,FALSE)-1,SUMPRODUCT(--(OFFSET($G$1,1,MATCH($C2,$G$1:$I$1,FALSE)-1,999)<>"")))


Book1
ABC
1ROWINDEXVENDOR
22111111VENDOR_1
33222222VENDOR_1
44333333VENDOR_3
55444444VENDOR_1
66555555VENDOR_2
77666666VENDOR_1
88777777VENDOR_2
99888888VENDOR_2
1010999999VENDOR_2
1111101010VENDOR_2
1212202020VENDOR_3
1313303030VENDOR_3
Orders


This is setup without the O365 UNIQUE, FILTER, SORT functions - now the drop down in D2 shows me 111111 / 222222 / 444444 / 666666 and the drop down in D7 shows me 333333 / 202020 / 303030
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,425
Office Version
  1. 365
Platform
  1. Windows
Cross posted Data validation formula to return list of orders based on vendor

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Cyberpunk001

New Member
Joined
Aug 27, 2018
Messages
13

ADVERTISEMENT

Hi tyija1995

Your solution is brilliant, thanks so much!

Just one small issue, when I tested I found that for some odd reason VENDOR_1 shows two blank spaces below the numbers in the list, however the other vendor lists does not?

Also, yes I do have UNIQUE, FILTER, SORT functions in my version, what are your thoughts apropos calculation timing using your proposed methods vs the UNIQUE, FILTER, SORT functions? The actual sheet I will use will keep adding numbers for each vendor(as mentioned up to 40 different vendors), so the dynamic range will become quite large later on, so in an ideal world I would like to minimise the time it takes for the sheet to calculate.

Hi Fluff, apologies and noted for future posts.
 

Cyberpunk001

New Member
Joined
Aug 27, 2018
Messages
13
Hi tyija1995

Your solution is brilliant, thanks so much!

Just one small issue, when I tested I found that for some odd reason VENDOR_1 shows two blank spaces below the numbers in the list, however the other vendor lists does not?

Also, yes I do have UNIQUE, FILTER, SORT functions in my version, what are your thoughts apropos calculation timing using your proposed methods vs the UNIQUE, FILTER, SORT functions? The actual sheet I will use will keep adding numbers for each vendor(as mentioned up to 40 different vendors), so the dynamic range will become quite large later on, so in an ideal world I would like to minimise the time it takes for the sheet to calculate.

Hi Fluff, apologies and noted for future posts.
tyija1995 I forgot to mention, I got around the two blank spaces by implementing the following equation: OFFSET(data,data,data,COUNTIFS(ORDERS!$C:$C,$C2)) where "data" refers to your part of the formula.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
781
Office Version
  1. 365
Platform
  1. Windows
Hi tyija1995

Your solution is brilliant, thanks so much!

Just one small issue, when I tested I found that for some odd reason VENDOR_1 shows two blank spaces below the numbers in the list, however the other vendor lists does not?

Also, yes I do have UNIQUE, FILTER, SORT functions in my version, what are your thoughts apropos calculation timing using your proposed methods vs the UNIQUE, FILTER, SORT functions? The actual sheet I will use will keep adding numbers for each vendor(as mentioned up to 40 different vendors), so the dynamic range will become quite large later on, so in an ideal world I would like to minimise the time it takes for the sheet to calculate.

Hi Fluff, apologies and noted for future posts.

Hi Cyber,

I am a bit unsure why you had the blanks in the DV drop-down lists for VENDOR_1 only? The SUMPRODUCT part of the DV list formula should eradicate any blanks due to the IFERROR catch statement.

FILTER and UNIQUE will ease up finding the unique vendors and their indexes. If I were you i'd put the Orders tab in to a table (Table1 let's say)

Then in that case, you could have F1 still as "Unique Vendors:" and set G1 = TRANSPOSE(UNIQUE(Table1[VENDOR])) which will grow horizontally along the header row on the Test tab, and dynamically with the Vendors that are inputted into Table1 (Orders tab table)

Then in cell G2 you can go for
=FILTER(Table1[INDEX],Test!G1=Table1[VENDOR])
and copy this across to I2 to generate the lists. (This should be the only manual part of the task, if you have more than 3 cols (J,K,L... etc) then drag the formula along further. You could pre-empt this and drag it along many columns making use of the if_empty FILTER parameter as a blank ("") so that it hides any #CALC! errors.

One note about the DV rule I used
OFFSET($G$1,1,MATCH($C2,$G$1:$I$1,FALSE)-1,SUMPRODUCT(--(OFFSET($G$1,1,MATCH($C2,$G$1:$I$1,FALSE)-1,999)<>"")))

This falters after the 3rd vendor as I am using the range of $G$1:$I$1 as the header range (spans 3 cols), just change the latter bound to an increased column number to generate a bigger range. (E.G. $G$1:$ZZ$1 should suffice)

OFFSET($G$1,1,MATCH($C2,$G$1:$ZZ$1,FALSE)-1,SUMPRODUCT(--(OFFSET($G$1,1,MATCH($C2,$G$1:$ZZ$1,FALSE)-1,999)<>"")))

At this point I'd make the table on the Test tab in to an actual table, then when inputting the DV rule directly above this line, highlight the ORDER NO column.

When a new record is added to this table, the DV rule will now apply dynamically to the added row and pick up the rule! Providing fully automated solution :)

Book1
FGHI
1Unique Vendors:VENDOR_1VENDOR_3VENDOR_2
2111111333333555555
3222222202020777777
4444444303030888888
5666666999999
6101010
Test
Cell Formulas
RangeFormula
G1:I1G1=TRANSPOSE(UNIQUE(Table1[VENDOR]))
G2:G5,I2:I6,H2:H4G2=FILTER(Table1[INDEX],Test!G1=Table1[VENDOR])
Dynamic array formulas.
 
Last edited:

Cyberpunk001

New Member
Joined
Aug 27, 2018
Messages
13
Hi tyija1995

I agree with you on your DV drop down list solution provided earlier, seems I made an error when trying out your formula which led to the blank spaces. I tried again and it worked exactly as you intended.

Thanks, my actual sheet is very similar to what you propose, and I do use dynamic tables. The formulas you gave has helped quite a lot to automate new vendors as it is booked on the table.

I was just getting stuck on the DV drop down list, I had tried so many different ways but could not crack it.

Thanks again for your help :)
 

Forum statistics

Threads
1,137,354
Messages
5,681,002
Members
419,948
Latest member
Sbakker1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top