Hello All,
I have UTFSE however i really am having difficulty explaining this issue so please bear with me.
I have a list of products and its used for Data Validation, the idea is i can just select the product name, the next step is to be offered a list that will only show me the relevant product options for each product.
basically the issue im running into is 1 product can have multiple options, so Product X can come in Size A and Size B, but Product X In Size A Comes from supplier1, Product X In Size B comes from supplier2 and Product X Can come in Both Size A and B from Supplier3.
So i have been using named ranges, merged cells and Data validation for this, below is what my setup looks like
Column A
Product X
Product Y
Product Z
The above list is in a work book on its own, its just the list that allows me to select the product and then based on my selection i want it to show my the relevant product options (Size A or Size B or Both) - the idea here is to limit user error by only allowing real product combination (Not combinations that dont exist, like product x of supplier2 in size A)
So here is where it gets tricky, on a different workbook i have more Data validation lists and they look like this
Column A Column B
1 - Product X - Size A
2 - Product Y - Size B
3 - Product Z - Size A
4 - (Merged) - Size B
I thought i was being clever merging rows 3 & 4 for Product Z as it has mutiple choices, but an issue i run into here is that automatically defining names using the data on the left does not apply a range to row 4 in this example, that has to be done manually, so another and i think better idea is to have repeating info, so in the instance of just Product Z it would go like this:
Column A Column B
1 - Product X - Size A
2 - Product Y - Size B
3 - Product Z - Size A
4 - Product Z - Size B
But then im going to get the same products appearing in the same list but returning different values which will also cause big issues, so at this point im stumped, especially as this is just the first hurdle because then i have to so the same again with the suppliers which makes it much more complicated
Column A Column B Column C
Product X - Size A - Supplier 1
- Supplier 2
Product Y - Size B - Supplier 2
Product Z - Size A - Supplier 2
Supplier 3
Product Z - Size B - Supplier 2
Supplier 3
Finally in the above example i didnt include row numbers as they will change depending on the solution, i could merge the cells but merged cells dont work in Tables and dont work with automatically defined names (which is a must as the data sets are HUGE in my actual real sheet im working on) OR if i don't used merged cells i will have to find away of preventing duplicate entries appearing in the list, i need one entry (product X) to appear in the list with multiple values without using merged cells, this will also allow me to use a table (Ctrl-t type of table) whic is great as those types of table automatically increase a range when a new entry is added, so tables are definitely the preferred way of storing the data.
****it im finding this hard to explain, so if you guys want a video of what i mean or a screenshot i can make one or something, just let me know, if you do indeed need a workbook im sure i can cook up an example too, but ANY help would be great, i also forgot to say, Im giving everything in collum B a range name made automatically from the same row but in Collum A, im then using INDIRECT to create the dynamic reference so my lists range is correct.
Thanks!!!!
Corin
I have UTFSE however i really am having difficulty explaining this issue so please bear with me.
I have a list of products and its used for Data Validation, the idea is i can just select the product name, the next step is to be offered a list that will only show me the relevant product options for each product.
basically the issue im running into is 1 product can have multiple options, so Product X can come in Size A and Size B, but Product X In Size A Comes from supplier1, Product X In Size B comes from supplier2 and Product X Can come in Both Size A and B from Supplier3.
So i have been using named ranges, merged cells and Data validation for this, below is what my setup looks like
Column A
Product X
Product Y
Product Z
The above list is in a work book on its own, its just the list that allows me to select the product and then based on my selection i want it to show my the relevant product options (Size A or Size B or Both) - the idea here is to limit user error by only allowing real product combination (Not combinations that dont exist, like product x of supplier2 in size A)
So here is where it gets tricky, on a different workbook i have more Data validation lists and they look like this
Column A Column B
1 - Product X - Size A
2 - Product Y - Size B
3 - Product Z - Size A
4 - (Merged) - Size B
I thought i was being clever merging rows 3 & 4 for Product Z as it has mutiple choices, but an issue i run into here is that automatically defining names using the data on the left does not apply a range to row 4 in this example, that has to be done manually, so another and i think better idea is to have repeating info, so in the instance of just Product Z it would go like this:
Column A Column B
1 - Product X - Size A
2 - Product Y - Size B
3 - Product Z - Size A
4 - Product Z - Size B
But then im going to get the same products appearing in the same list but returning different values which will also cause big issues, so at this point im stumped, especially as this is just the first hurdle because then i have to so the same again with the suppliers which makes it much more complicated
Column A Column B Column C
Product X - Size A - Supplier 1
- Supplier 2
Product Y - Size B - Supplier 2
Product Z - Size A - Supplier 2
Supplier 3
Product Z - Size B - Supplier 2
Supplier 3
Finally in the above example i didnt include row numbers as they will change depending on the solution, i could merge the cells but merged cells dont work in Tables and dont work with automatically defined names (which is a must as the data sets are HUGE in my actual real sheet im working on) OR if i don't used merged cells i will have to find away of preventing duplicate entries appearing in the list, i need one entry (product X) to appear in the list with multiple values without using merged cells, this will also allow me to use a table (Ctrl-t type of table) whic is great as those types of table automatically increase a range when a new entry is added, so tables are definitely the preferred way of storing the data.
****it im finding this hard to explain, so if you guys want a video of what i mean or a screenshot i can make one or something, just let me know, if you do indeed need a workbook im sure i can cook up an example too, but ANY help would be great, i also forgot to say, Im giving everything in collum B a range name made automatically from the same row but in Collum A, im then using INDIRECT to create the dynamic reference so my lists range is correct.
Thanks!!!!
Corin
