Dynamic Valuelist based on selected dropdown
I'm fairly new to excel. I have googled this with no help. I will try to explain this the best way I can.
I have a field that I created a dropdown from a named range. I then want to use the value selected to create another dropdown list, but its filtered to only display certain values from the table. I will then use that to do vlookups and populate the rest of my layout. I have tried INDIRECT and VLOOKUP formulas and cannot find the right combination.
So as this table grows with different configurations, I want to be able to select from the list in A4 (which is pulled from H11-H13) and then it filters and only shows values from B11-B40 that match in column A of that table. I think this is possible to do, but I've been struggling with it for the past few days and I am not getting anywhere. I am hoping my question makes sense. Please let me know if you need more info.
Book1 |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
3 | PLAYBOOK | POSITION | | | | | | |
---|
4 | SFS | | | | | | | |
---|
5 | | SFS - 2x2 | | | | | | |
---|
6 | | | | | | | | |
---|
7 | | | | | | | | |
---|
8 | | | | | | | | |
---|
9 | | | | | | | | |
---|
10 | PLAYBOOK | POSITION | NODE CONFIG | | | | | PLAYBOOK |
---|
11 | 16CH/40CH MUXES | NODE 1 - 1x2 | 1x2 | | | | | 16CH/40CH MUXES |
---|
12 | 16CH/40CH MUXES | NODE 2 - 1x2 | 1x2 | | | | | SFS |
---|
13 | 16CH/40CH MUXES | NODE 3 - 1x2 | 1x2 | | | | | ARRIS OPTION A - 16WL DIRECT SFS |
---|
14 | 16CH/40CH MUXES | NODE 4 - 1x2 | 1x2 | | | | | |
---|
15 | 16CH/40CH MUXES | NODE 1 - 2x2 | 2x2 | | | | | |
---|
16 | 16CH/40CH MUXES | NODE 2 - 2x2 | 2x2 | | | | | |
---|
17 | 16CH/40CH MUXES | NODE 3 - 2x2 | 2x2 | | | | | |
---|
18 | 16CH/40CH MUXES | NODE 4 - 2x2 | 2x2 | | | | | |
---|
19 | 16CH/40CH MUXES | NODE 1 - 2x4 | 2x4 | | | | | |
---|
20 | 16CH/40CH MUXES | NODE 2 - 2x4 | 2x4 | | | | | |
---|
21 | 16CH/40CH MUXES | NODE 3 - 2x4 | 2x4 | | | | | |
---|
22 | 16CH/40CH MUXES | NODE 4 - 2x4 | 2x4 | | | | | |
---|
23 | 16CH/40CH MUXES | NODE 1 - 3x3 | 3x3 | | | | | |
---|
24 | 16CH/40CH MUXES | NODE 2 - 3x3 | 3x3 | | | | | |
---|
25 | 16CH/40CH MUXES | NODE 3 - 3x3 | 3x3 | | | | | |
---|
26 | 16CH/40CH MUXES | NODE 4 - 3x3 | 3x3 | | | | | |
---|
27 | 16CH/40CH MUXES | NODE 1 - 3x4 | 3x4 | | | | | |
---|
28 | 16CH/40CH MUXES | NODE 2 - 3x4 | 3x4 | | | | | |
---|
29 | 16CH/40CH MUXES | NODE 3 - 3x4 | 3x4 | | | | | |
---|
30 | 16CH/40CH MUXES | NODE 4 - 3x4 | 3x4 | | | | | |
---|
31 | 16CH/40CH MUXES | NODE 1 - 4x4 | 4x4 | | | | | |
---|
32 | 16CH/40CH MUXES | NODE 2 - 4x4 | 4x4 | | | | | |
---|
33 | 16CH/40CH MUXES | NODE 3 - 4x4 | 4x4 | | | | | |
---|
34 | 16CH/40CH MUXES | NODE 4 - 4x4 | 4x4 | | | | | |
---|
35 | SFS | SFS - 2x2 | SFS - 2x2 | | | | | |
---|
36 | SFS | SFS - 4x4 | SFS - 4x4 | | | | | |
---|
37 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 1 - 4x4 | 4x4 | | | | | |
---|
38 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 2 - 4x4 | 4x4 | | | | | |
---|
39 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 3 - 4x4 | 4x4 | | | | | |
---|
40 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 4 - 4x4 | 4x4 | | | | | |
---|
|
---|
Excel Message board.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J |
---|
1 | PLAYBOOK | POSITION | NODE CONFIG | | | | | | | |
---|
2 | 16CH/40CH MUXES | NODE 4 - 2x2 | 2x2 | | | =OFFSET($J$9,,,COUNTA($J$9:$J$38)-COUNTBLANK($J$9:$J$38)) | | | | |
---|
3 | | | | | | | | | | |
---|
4 | | =OFFSET($I$9,,,COUNTA($I$9:$I$38)-COUNTBLANK($I$9:$I$38)) | | | | | | | | |
---|
5 | dropdown formular for Playbook | | | | | | | | | |
---|
6 | =OFFSET($H$9,,,COUNTA($H$9:$H$38)-COUNTBLANK($H$9:$H$38)) | | | | | | | | | |
---|
7 | | | | | | | | Unique Playbook Name | Unique position Depend on A2 | |
---|
8 | PLAYBOOK | POSITION | NODE CONFIG | | | | | PLAYBOOK | POSITION | NODE CONFIG |
---|
9 | 16CH/40CH MUXES | NODE 1 - 1x2 | 1x2 | | | | | 16CH/40CH MUXES | NODE 1 - 1x2 | 2x2 |
---|
10 | 16CH/40CH MUXES | NODE 2 - 1x2 | 1x2 | | | | | SFS | NODE 2 - 1x2 | |
---|
11 | 16CH/40CH MUXES | NODE 3 - 1x2 | 1x2 | | | | | ARRIS OPTION A - 16WL DIRECT SFS | NODE 3 - 1x2 | |
---|
12 | 16CH/40CH MUXES | NODE 4 - 1x2 | 1x2 | | | | | | NODE 4 - 1x2 | |
---|
13 | 16CH/40CH MUXES | NODE 1 - 2x2 | 2x2 | | | | | | NODE 1 - 2x2 | |
---|
14 | 16CH/40CH MUXES | NODE 2 - 2x2 | 2x2 | | | | | | NODE 2 - 2x2 | |
---|
15 | 16CH/40CH MUXES | NODE 3 - 2x2 | 2x2 | | | | | | NODE 3 - 2x2 | |
---|
16 | 16CH/40CH MUXES | NODE 4 - 2x2 | 2x2 | | | | | | NODE 4 - 2x2 | |
---|
17 | 16CH/40CH MUXES | NODE 1 - 2x4 | 2x4 | | | | | | NODE 1 - 2x4 | |
---|
18 | 16CH/40CH MUXES | NODE 2 - 2x4 | 2x4 | | | | | | NODE 2 - 2x4 | |
---|
19 | 16CH/40CH MUXES | NODE 3 - 2x4 | 2x4 | | | | | | NODE 3 - 2x4 | |
---|
20 | 16CH/40CH MUXES | NODE 4 - 2x4 | 2x4 | | | | | | NODE 4 - 2x4 | |
---|
21 | 16CH/40CH MUXES | NODE 1 - 3x3 | 3x3 | | | | | | NODE 1 - 3x3 | |
---|
22 | 16CH/40CH MUXES | NODE 2 - 3x3 | 3x3 | | | | | | NODE 2 - 3x3 | |
---|
23 | 16CH/40CH MUXES | NODE 3 - 3x3 | 3x3 | | | | | | NODE 3 - 3x3 | |
---|
24 | 16CH/40CH MUXES | NODE 4 - 3x3 | 3x3 | | | | | | NODE 4 - 3x3 | |
---|
25 | 16CH/40CH MUXES | NODE 1 - 3x4 | 3x4 | | | | | | NODE 1 - 3x4 | |
---|
26 | 16CH/40CH MUXES | NODE 2 - 3x4 | 3x4 | | | | | | NODE 2 - 3x4 | |
---|
27 | 16CH/40CH MUXES | NODE 3 - 3x4 | 3x4 | | | | | | NODE 3 - 3x4 | |
---|
28 | 16CH/40CH MUXES | NODE 4 - 3x4 | 3x4 | | | | | | NODE 4 - 3x4 | |
---|
29 | 16CH/40CH MUXES | NODE 1 - 4x4 | 4x4 | | | | | | NODE 1 - 4x4 | |
---|
30 | 16CH/40CH MUXES | NODE 2 - 4x4 | 4x4 | | | | | | NODE 2 - 4x4 | |
---|
31 | 16CH/40CH MUXES | NODE 3 - 4x4 | 4x4 | | | | | | NODE 3 - 4x4 | |
---|
32 | 16CH/40CH MUXES | NODE 4 - 4x4 | 4x4 | | | | | | NODE 4 - 4x4 | |
---|
33 | SFS | SFS - 2x2 | SFS - 2x2 | | | | | | | |
---|
34 | SFS | SFS - 4x4 | SFS - 4x4 | | | | | | | |
---|
35 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 1 - 4x4 | 4x4 | | | | | | | |
---|
36 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 2 - 4x4 | 4x4 | | | | | | | |
---|
37 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 3 - 4x4 | 4x4 | | | | | | | |
---|
38 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 4 - 4x4 | 4x4 | | | | | | | |
---|
39 | | | | | | | | | | |
---|
40 | | | | | | | | | | |
---|
|
---|
Hi Try This.! It is not One step solution .Hope you can use this approach!
-
Screenshot (35).png
60.7 KB
· Views: 18
I suggest that you update your
Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
If you have 365 maybe
+Fluff v2.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | | | | | | | Playbook | Position |
---|
2 | | | | | | | 16CH/40CH MUXES | NODE 1 - 1x2 |
---|
3 | | | | | | | | NODE 2 - 1x2 |
---|
4 | | | | | | | | NODE 3 - 1x2 |
---|
5 | | | | | | | | NODE 4 - 1x2 |
---|
6 | | | | | | | | NODE 1 - 2x2 |
---|
7 | | | | | | | | NODE 2 - 2x2 |
---|
8 | PLAYBOOK | POSITION | NODE CONFIG | | 16CH/40CH MUXES | | | NODE 3 - 2x2 |
---|
9 | 16CH/40CH MUXES | NODE 1 - 1x2 | 1x2 | | SFS | | | NODE 4 - 2x2 |
---|
10 | 16CH/40CH MUXES | NODE 2 - 1x2 | 1x2 | | ARRIS OPTION A - 16WL DIRECT SFS | | | NODE 1 - 2x4 |
---|
11 | 16CH/40CH MUXES | NODE 3 - 1x2 | 1x2 | | | | | NODE 2 - 2x4 |
---|
12 | 16CH/40CH MUXES | NODE 4 - 1x2 | 1x2 | | | | | NODE 3 - 2x4 |
---|
13 | 16CH/40CH MUXES | NODE 1 - 2x2 | 2x2 | | | | | NODE 4 - 2x4 |
---|
14 | 16CH/40CH MUXES | NODE 2 - 2x2 | 2x2 | | | | | NODE 1 - 3x3 |
---|
15 | 16CH/40CH MUXES | NODE 3 - 2x2 | 2x2 | | | | | NODE 2 - 3x3 |
---|
16 | 16CH/40CH MUXES | NODE 4 - 2x2 | 2x2 | | | | | NODE 3 - 3x3 |
---|
17 | 16CH/40CH MUXES | NODE 1 - 2x4 | 2x4 | | | | | NODE 4 - 3x3 |
---|
18 | 16CH/40CH MUXES | NODE 2 - 2x4 | 2x4 | | | | | NODE 1 - 3x4 |
---|
19 | 16CH/40CH MUXES | NODE 3 - 2x4 | 2x4 | | | | | NODE 2 - 3x4 |
---|
20 | 16CH/40CH MUXES | NODE 4 - 2x4 | 2x4 | | | | | NODE 3 - 3x4 |
---|
21 | 16CH/40CH MUXES | NODE 1 - 3x3 | 3x3 | | | | | NODE 4 - 3x4 |
---|
22 | 16CH/40CH MUXES | NODE 2 - 3x3 | 3x3 | | | | | NODE 1 - 4x4 |
---|
23 | 16CH/40CH MUXES | NODE 3 - 3x3 | 3x3 | | | | | NODE 2 - 4x4 |
---|
24 | 16CH/40CH MUXES | NODE 4 - 3x3 | 3x3 | | | | | NODE 3 - 4x4 |
---|
25 | 16CH/40CH MUXES | NODE 1 - 3x4 | 3x4 | | | | | NODE 4 - 4x4 |
---|
26 | 16CH/40CH MUXES | NODE 2 - 3x4 | 3x4 | | | | | |
---|
27 | 16CH/40CH MUXES | NODE 3 - 3x4 | 3x4 | | | | | |
---|
28 | 16CH/40CH MUXES | NODE 4 - 3x4 | 3x4 | | | | | |
---|
29 | 16CH/40CH MUXES | NODE 1 - 4x4 | 4x4 | | | | | |
---|
30 | 16CH/40CH MUXES | NODE 2 - 4x4 | 4x4 | | | | | |
---|
31 | 16CH/40CH MUXES | NODE 3 - 4x4 | 4x4 | | | | | |
---|
32 | 16CH/40CH MUXES | NODE 4 - 4x4 | 4x4 | | | | | |
---|
33 | SFS | SFS - 2x2 | SFS - 2x2 | | | | | |
---|
34 | SFS | SFS - 4x4 | SFS - 4x4 | | | | | |
---|
35 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 1 - 4x4 | 4x4 | | | | | |
---|
36 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 2 - 4x4 | 4x4 | | | | | |
---|
37 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 3 - 4x4 | 4x4 | | | | | |
---|
38 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 4 - 4x4 | 4x4 | | | | | |
---|
39 | | | | | | | | |
---|
|
---|
I have tried both suggustions above and I haven't really been able to make either work. I changed my table slightly to work better with another issue I was having, so I have adjusted the formulas. I cannot get the "POSITION" column to work. I am not sure what the issue is. It must be something simple.
I was able to get farther with
@alz suggestion. I do have a couple of questions about it.
- It doesn't seem like it works in tables. So will I need to expand the formula to cover more cells as I get more config options in the future?
- I need to be able to have 4 rows to choose options in. The playbook will always stay the same. The only variable is CONFIG and POSITION. The position field should be a number between 1-4.
I've updated my sheet and this is what I am currently working with. I plan to add more fields as I build out the CONF table.
Let me know if this makes sense. Thanks again for the great responses! As I am new, I am still googling and trying to learn as much as possible.
Book2.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
---|
1 | | PLAYBOOK | NODE CONFIG | POSITION | | | | | | | | | | |
---|
2 | 1 | 16CH/40CH MUXES | 2x4 | | | | | | | | | | | |
---|
3 | 2 | | 1x2 | | | | | | | | | | | |
---|
4 | 3 | | 3x4 | | | | | | | | | | | |
---|
5 | 4 | | 4x4 | | | | | | | | | | | |
---|
6 | | | | | | | | | | | | | | |
---|
7 | | | | | | PLAYBOOK | | | | CONFIGURATIONS |
---|
8 | | | | | | | |
---|
9 | | | | | | NAME | | PLAYBOOK | CONFIG | POSITION | | PLAYBOOK | CONFIG | POSITION |
---|
10 | | | | | | 16CH/40CH MUXES | | 16CH/40CH MUXES | 1x2 | | | 16CH/40CH MUXES | 1x2 | NODE 1 |
---|
11 | | | | | | SFS - LEGACY | | OPTION B | 2x4 | | | 16CH/40CH MUXES | 1x2 | NODE 2 |
---|
12 | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | | OPTION C | 3x4 | | | 16CH/40CH MUXES | 1x2 | NODE 3 |
---|
13 | | | | | | OPTION B | | SFS - LEGACY | 4x4 | | | 16CH/40CH MUXES | 1x2 | NODE 4 |
---|
14 | | | | | | OPTION C | | ARRIS OPTION A - 16WL DIRECT SFS | | | | OPTION B | 2x2 | NODE 1 |
---|
15 | | | | | | | | | | | | OPTION B | 2x2 | NODE 2 |
---|
16 | | | | | | | | | | | | OPTION B | 2x2 | NODE 3 |
---|
17 | | | | | | | | | | | | OPTION B | 2x2 | NODE 4 |
---|
18 | | | | | | | | | | | | 16CH/40CH MUXES | 2x4 | NODE 1 |
---|
19 | | | | | | | | | | | | 16CH/40CH MUXES | 2x4 | NODE 2 |
---|
20 | | | | | | | | | | | | 16CH/40CH MUXES | 2x4 | NODE 3 |
---|
21 | | | | | | | | | | | | 16CH/40CH MUXES | 2x4 | NODE 4 |
---|
22 | | | | | | | | | | | | OPTION C | 3x3 | NODE 1 |
---|
23 | | | | | | | | | | | | OPTION C | 3x3 | NODE 2 |
---|
24 | | | | | | | | | | | | OPTION C | 3x3 | NODE 3 |
---|
25 | | | | | | | | | | | | OPTION C | 3x3 | NODE 4 |
---|
26 | | | | | | | | | | | | 16CH/40CH MUXES | 3x4 | NODE 1 |
---|
27 | | | | | | | | | | | | 16CH/40CH MUXES | 3x4 | NODE 2 |
---|
28 | | | | | | | | | | | | 16CH/40CH MUXES | 3x4 | NODE 3 |
---|
29 | | | | | | | | | | | | 16CH/40CH MUXES | 3x4 | NODE 4 |
---|
30 | | | | | | | | | | | | 16CH/40CH MUXES | 4x4 | NODE 1 |
---|
31 | | | | | | | | | | | | 16CH/40CH MUXES | 4x4 | NODE 2 |
---|
32 | | | | | | | | | | | | 16CH/40CH MUXES | 4x4 | NODE 3 |
---|
33 | | | | | | | | | | | | 16CH/40CH MUXES | 4x4 | NODE 4 |
---|
34 | | | | | | | | | | | | SFS - LEGACY | SFS | |
---|
35 | | | | | | | | | | | | SFS - LEGACY | SFS | |
---|
36 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 2x2 | NODE 1 |
---|
37 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 2x2 | NODE 2 |
---|
38 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 2x2 | NODE 3 |
---|
39 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 2x2 | NODE 4 |
---|
40 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 4x4 | NODE 1 |
---|
41 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 4x4 | NODE 2 |
---|
42 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 4x4 | NODE 3 |
---|
43 | | | | | | | | | | | | ARRIS OPTION A - 16WL DIRECT SFS | 4x4 | NODE 4 |
---|
|
---|
Excel Message board.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K |
---|
1 | PLAYBOOK | POSITION | NODE CONFIG | | | | | | | Unod =OFFSET(Unique[[#Headers],[NODE CONFIG]],1,,COUNTA(Unique[NODE CONFIG])-COUNTBLANK(Unique[NODE CONFIG])) | |
---|
2 | SFS | SFS - 4x4 | 2x4 | | | | | | Name for this column= Upos | | |
---|
3 | | | | | | | | Create name in Name manager | =OFFSET(Unique[[#Headers],[POSITION]],1,,COUNTA(Unique[POSITION])-COUNTBLANK(Unique[POSITION])) | | |
---|
4 | Dropdown Name = Uplay | Name = Upos | Name = Unod | | | | | Name for this column= Uplay | | | |
---|
5 | | | | | | | | =OFFSET(Unique[[#Headers],[PLAYBOOK]],1,,COUNTA(Unique[PLAYBOOK])-COUNTBLANK(Unique[PLAYBOOK])) | | | |
---|
6 | | | | | | | | | | | |
---|
7 | Table Name = Source | | | | | | Table Name = Unique | Unique Playbook Name | Unique position Depend on A2 | Unique position Depend on B2 | |
---|
8 | PLAYBOOK | POSITION | NODE CONFIG | | | | S.No | PLAYBOOK | POSITION | NODE CONFIG | |
---|
9 | 16CH/40CH MUXES | NODE 1 - 1x2 | 1x2 | | | | 1 | 16CH/40CH MUXES | SFS - 2x2 | SFS - 4x4 | |
---|
10 | 16CH/40CH MUXES | NODE 2 - 1x2 | 1x2 | | | | 2 | SFS | SFS - 4x4 | | |
---|
11 | 16CH/40CH MUXES | NODE 3 - 1x2 | 1x2 | | | | 3 | ARRIS OPTION A - 16WL DIRECT SFS | | | |
---|
12 | 16CH/40CH MUXES | NODE 4 - 1x2 | 1x2 | | | | 4 | | | | |
---|
13 | 16CH/40CH MUXES | NODE 1 - 2x2 | 2x2 | | | | 5 | | | | |
---|
14 | 16CH/40CH MUXES | NODE 2 - 2x2 | 2x2 | | | | 6 | | | | |
---|
15 | 16CH/40CH MUXES | NODE 3 - 2x2 | 2x2 | | | | 7 | | | | |
---|
16 | 16CH/40CH MUXES | NODE 4 - 2x2 | 2x2 | | | | 8 | | | | |
---|
17 | 16CH/40CH MUXES | NODE 1 - 2x4 | 2x4 | | | | 9 | | | | |
---|
18 | 16CH/40CH MUXES | NODE 2 - 2x4 | 2x4 | | | | 10 | | | | |
---|
19 | 16CH/40CH MUXES | NODE 3 - 2x4 | 2x4 | | | | 11 | | | | |
---|
20 | 16CH/40CH MUXES | NODE 4 - 2x4 | 2x4 | | | | 12 | | | | |
---|
21 | 16CH/40CH MUXES | NODE 1 - 3x3 | 3x3 | | | | 13 | | | | |
---|
22 | 16CH/40CH MUXES | NODE 2 - 3x3 | 3x3 | | | | 14 | | | | |
---|
23 | 16CH/40CH MUXES | NODE 3 - 3x3 | 3x3 | | | | 15 | | | | |
---|
24 | 16CH/40CH MUXES | NODE 4 - 3x3 | 3x3 | | | | 16 | | | | |
---|
25 | 16CH/40CH MUXES | NODE 1 - 3x4 | 3x4 | | | | 17 | | | | |
---|
26 | 16CH/40CH MUXES | NODE 2 - 3x4 | 3x4 | | | | 18 | | | | |
---|
27 | 16CH/40CH MUXES | NODE 3 - 3x4 | 3x4 | | | | 19 | | | | |
---|
28 | 16CH/40CH MUXES | NODE 4 - 3x4 | 3x4 | | | | 20 | | | | |
---|
29 | 16CH/40CH MUXES | NODE 1 - 4x4 | 4x4 | | | | 21 | | | | |
---|
30 | 16CH/40CH MUXES | NODE 2 - 4x4 | 4x4 | | | | 22 | | | | |
---|
31 | 16CH/40CH MUXES | NODE 3 - 4x4 | 4x4 | | | | 23 | | | | |
---|
32 | 16CH/40CH MUXES | NODE 4 - 4x4 | 4x4 | | | | 24 | | | | |
---|
33 | SFS | SFS - 2x2 | SFS - 2x2 | | | | 25 | | | | |
---|
34 | SFS | SFS - 4x4 | SFS - 4x4 | | | | 26 | | | | |
---|
35 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 1 - 4x4 | 4x4 | | | | 27 | | | | |
---|
36 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 2 - 4x4 | 4x4 | | | | 28 | | | | |
---|
37 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 3 - 4x4 | 4x4 | | | | 29 | | | | |
---|
38 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 4 - 4x4 | 4x4 | | | | 30 | | | | |
---|
|
---|
Hi .
Answer for Question(1) Formula also work with table .
you need to create name in name manager and use in dropdown list.
Excel Message board.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K |
---|
1 | PLAYBOOK | POSITION | NODE CONFIG | | | | | | | Unod =OFFSET(Unique[[#Headers],[NODE CONFIG]],1,,COUNTA(Unique[NODE CONFIG])-COUNTBLANK(Unique[NODE CONFIG])) | |
---|
2 | 16CH/40CH MUXES | NODE 2 - 2x2 | 2x4 | | | | | | Name for this column= Upos | | |
---|
3 | SFS | SFS - 4x4 | | | | | | Create name in Name manager | =OFFSET(Unique[[#Headers],[POSITION]],1,,COUNTA(Unique[POSITION])-COUNTBLANK(Unique[POSITION])) | | |
---|
4 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 4 - 4x4 | | | | | | Name for this column= Uplay | | | |
---|
5 | 16CH/40CH MUXES | NODE 3 - 4x4 | | | | | | =OFFSET(Unique[[#Headers],[PLAYBOOK]],1,,COUNTA(Unique[PLAYBOOK])-COUNTBLANK(Unique[PLAYBOOK])) | | | |
---|
6 | Dropdown Name = Uplay | Name = Upos | Name = Unod | | | | | | | | |
---|
7 | Table Name = Source | | $C$7 | | | | Table Name = Unique | Unique Playbook Name | Unique position Depend on A2 | Unique position Depend on B2 | |
---|
8 | PLAYBOOK | POSITION | NODE CONFIG | | | | S.No | PLAYBOOK | POSITION | NODE CONFIG | |
---|
9 | 16CH/40CH MUXES | NODE 1 - 1x2 | 1x2 | | | | 1 | 16CH/40CH MUXES | | | |
---|
10 | 16CH/40CH MUXES | NODE 2 - 1x2 | 1x2 | | | | 2 | SFS | | | |
---|
11 | 16CH/40CH MUXES | NODE 3 - 1x2 | 1x2 | | | | 3 | ARRIS OPTION A - 16WL DIRECT SFS | | | |
---|
12 | 16CH/40CH MUXES | NODE 4 - 1x2 | 1x2 | | | | 4 | | | | |
---|
13 | 16CH/40CH MUXES | NODE 1 - 2x2 | 2x2 | | | | 5 | | | | |
---|
14 | 16CH/40CH MUXES | NODE 2 - 2x2 | 2x2 | | | | 6 | | | | |
---|
15 | 16CH/40CH MUXES | NODE 3 - 2x2 | 2x2 | | | | 7 | | | | |
---|
16 | 16CH/40CH MUXES | NODE 4 - 2x2 | 2x2 | | | | 8 | | | | |
---|
17 | 16CH/40CH MUXES | NODE 1 - 2x4 | 2x4 | | | | 9 | | | | |
---|
18 | 16CH/40CH MUXES | NODE 2 - 2x4 | 2x4 | | | | 10 | | | | |
---|
19 | 16CH/40CH MUXES | NODE 3 - 2x4 | 2x4 | | | | 11 | | | | |
---|
20 | 16CH/40CH MUXES | NODE 4 - 2x4 | 2x4 | | | | 12 | | | | |
---|
21 | 16CH/40CH MUXES | NODE 1 - 3x3 | 3x3 | | | | 13 | | | | |
---|
22 | 16CH/40CH MUXES | NODE 2 - 3x3 | 3x3 | | | | 14 | | | | |
---|
23 | 16CH/40CH MUXES | NODE 3 - 3x3 | 3x3 | | | | 15 | | | | |
---|
24 | 16CH/40CH MUXES | NODE 4 - 3x3 | 3x3 | | | | 16 | | | | |
---|
25 | 16CH/40CH MUXES | NODE 1 - 3x4 | 3x4 | | | | 17 | | | | |
---|
26 | 16CH/40CH MUXES | NODE 2 - 3x4 | 3x4 | | | | 18 | | | | |
---|
27 | 16CH/40CH MUXES | NODE 3 - 3x4 | 3x4 | | | | 19 | | | | |
---|
28 | 16CH/40CH MUXES | NODE 4 - 3x4 | 3x4 | | | | 20 | | | | |
---|
29 | 16CH/40CH MUXES | NODE 1 - 4x4 | 4x4 | | | | 21 | | | | |
---|
30 | 16CH/40CH MUXES | NODE 2 - 4x4 | 4x4 | | | | 22 | | | | |
---|
31 | 16CH/40CH MUXES | NODE 3 - 4x4 | 4x4 | | | | 23 | | | | |
---|
32 | 16CH/40CH MUXES | NODE 4 - 4x4 | 4x4 | | | | 24 | | | | |
---|
33 | SFS | SFS - 2x2 | SFS - 2x2 | | | | 25 | | | | |
---|
34 | SFS | SFS - 4x4 | SFS - 4x4 | | | | 26 | | | | |
---|
35 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 1 - 4x4 | 4x4 | | | | 27 | | | | |
---|
36 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 2 - 4x4 | 4x4 | | | | 28 | | | | |
---|
37 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 3 - 4x4 | 4x4 | | | | 29 | | | | |
---|
38 | ARRIS OPTION A - 16WL DIRECT SFS | NODE 4 - 4x4 | 4x4 | | | | 30 | | | | |
---|
39 | | | | | | | | | | | |
---|
|
---|
Answer(2)
(From unique table ) position Columns calculate depend on A2 and Node config column is calculate depend on C2. we can trick the formula by using volitile function
Cell("Address") and refer with
indirect formula So whenever you click or enter
Cell("Address") will show the address of that cells So that the position Columns and Node config column will calculate depend on that active cell
( For example . you click on C10 formula will calculate depend on C10. Writing something on D10 formula will calculate depend on D10)
you need to careful one thing , ---- after you click on A3 you need to select on B3
---- if you want to input Data on B4 click A4 first and then input B4
- Threads
- 1,226,876
- Messages
- 6,193,460
- Members
- 453,801
- Latest member
- 777nycole