Scotster
Board Regular
- Joined
- May 29, 2017
- Messages
- 59
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
I'm fumbling around in the dark with this one as I can't get anything in play here that isn't a circular reference (Maybe that's the only solution though).
So what I want to be able to do is create a dynamic list of parts based on their appearances in a table that aren't 0. The table looks as follows:
From here, what I would like is a dynamic list depending on the values in the table. With PartX not having any quantities against it, it won't appear in the list at all. The list will look as follows:
Is there a way of dynamically creating the list above without using VBA? I don't mind using a VBA array if that's the solution, but I would rather do it with a formula if I could.
So what I want to be able to do is create a dynamic list of parts based on their appearances in a table that aren't 0. The table looks as follows:
| PartNo | Wk1 | Wk2 | Wk3 | Wk4 | Wk5 | Wk6 | Wk7 | Wk8 | Wk9 |
| PartX | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Part1 | 0 | 5 | 0 | 15 | 0 | 1000 | 0 | 0 | 0 |
| Part2 | 10 | 0 | 10 | 0 | 10 | 0 | 10 | 0 | 10 |
| Part3 | 0 | 10 | 0 | 10 | 0 | 10 | 0 | 10 | 0 |
| Part4 | 0 | 0 | 0 | 0 | 1000 | 0 | 0 | 0 | 0 |
From here, what I would like is a dynamic list depending on the values in the table. With PartX not having any quantities against it, it won't appear in the list at all. The list will look as follows:
| Part No |
| Part1 |
| Part1 |
| Part1 |
| Part2 |
| Part2 |
| Part2 |
| Part2 |
| Part2 |
| Part3 |
| Part3 |
| Part3 |
| Part3 |
| Part4 |
Is there a way of dynamically creating the list above without using VBA? I don't mind using a VBA array if that's the solution, but I would rather do it with a formula if I could.
