Hi all,
I'm coming back here because you guys helped me greatly in the past. I'm now running into an issue of which I can't find the solution online, I feel like I'm getting close but I'm missing something.
So I'm making a template for my colleagues for new article requests. One of the fields that has to be filled is the Product Hierarchy (PH). Unfortunately the PH exists out of 7*2 characters. So on the last 2 levels the list has like 15k options each, therefore I can't make it a nice horizontal table in which I can basicly refer to a next column or something.
So this is a simplified version but the data looks roughly as follow:
So what do I want? A drop down of the first level I can make myself, it's easy and very limited. But then comes the problem, once the user selects the first level in another column I want the next drop down to be only the list that is a length of 4 (0101, 0102, 0103, 0201, 0202) but also in which the left 2 characters is the same as the 2 characteres chosen in the first level.
And of course the dropdown should only show the list of 6 characters long, in which the first 2+2 characters come from level 1 and level 2.
I've tried to play with an offset - match - countif formula, but if I can't put the conditions in I can't make it work.
And lastly, not sure if it makes it more difficult or not, while both the PH as the description comes into the template, the user should be choosing from the description, then I will use a xlookup to find the right PH value, and that value can then be used for the next level etc. :D
Would anyone be able to get me on the right path?
I'm coming back here because you guys helped me greatly in the past. I'm now running into an issue of which I can't find the solution online, I feel like I'm getting close but I'm missing something.
So I'm making a template for my colleagues for new article requests. One of the fields that has to be filled is the Product Hierarchy (PH). Unfortunately the PH exists out of 7*2 characters. So on the last 2 levels the list has like 15k options each, therefore I can't make it a nice horizontal table in which I can basicly refer to a next column or something.
So this is a simplified version but the data looks roughly as follow:
| PH | Description |
| 01 | Core |
| 02 | 3rd Party |
| 0101 | Normal |
| 0102 | Sets |
| 0103 | Marketing |
| 0201 | Normal |
| 0202 | Sets |
| 010101 | Finished |
| 010201 | Finished |
| 010202 | Semi Finished |
| 010301 | Finished |
| 010303 | Tester |
| 020101 | Finished |
| 020102 | Semi Finished |
| 020201 | Finished |
So what do I want? A drop down of the first level I can make myself, it's easy and very limited. But then comes the problem, once the user selects the first level in another column I want the next drop down to be only the list that is a length of 4 (0101, 0102, 0103, 0201, 0202) but also in which the left 2 characters is the same as the 2 characteres chosen in the first level.
And of course the dropdown should only show the list of 6 characters long, in which the first 2+2 characters come from level 1 and level 2.
I've tried to play with an offset - match - countif formula, but if I can't put the conditions in I can't make it work.
And lastly, not sure if it makes it more difficult or not, while both the PH as the description comes into the template, the user should be choosing from the description, then I will use a xlookup to find the right PH value, and that value can then be used for the next level etc. :D
Would anyone be able to get me on the right path?
