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
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
