I have listed 5 Equipment and Models:
| EQUIPMENT-01 | MODEL | EQUIPMENT-02 | MODEL | EQUIPMENT-03 | MODEL | EQUIPMENT-04 | MODEL | EQUIPMENT-05 | MODEL | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| WHEEL LOADER | CAT 966H | WHEEL LOADER | CAT 966G | BULLDOZER | CAT D8R | BULLDOZER | CAT D9R | MOTOR GRADER | CAT D9R | |||||||||
| MOTOR GRADER | motor grader 01 | MOTOR GRADER | MOTOR GRADER | MOTOR GRADER | MOTOR GRADER | |||||||||||||
| BULLDOZER | bulldozer 01 |
In the Summary, it should display the Models based on the Equipment selected.
I have a formula where I can display one Model only but I need to display as many models based on the selected Equipment.
I have shared a sample spreadsheet.
3
Use filter(), like this:
=let(
r, transpose(filter(B5:U8, B2:U2 = W3)),
filter(r, len(choosecols(r, 1)))
)
Cell W3 should contain a dropdown that lets you choose the equipment. You can set up the dropdown by reading options from a range you add with this formula:
=transpose(filter(B4:U4, len(B4:U4), B4:U4 <> "MODEL"))
Row B2:U2 should repeat the equipment label, like this:
=B4
=B2
| SUMMARY RESULTS | ||
|---|---|---|
| choose equipment: | EQUIPMENT-01 | |
| WHEEL LOADER | MOTOR GRADER | BULLDOZER |
| CAT 966H | motor grader 01 | bulldozer 01 |
See the test tab.
Going forward, you may want to use a row-oriented data layout, like this:
| Name | Subject | Quarter | Score |
|---|---|---|---|
| Marie Curie | Chemistry | Q1 | 100 |
| Albert Einstein | Chemistry | Q1 | 34 |
| Marie Curie | Math | Q1 | 88 |
| Albert Einstein | Math | Q1 | 66 |
This loosely corresponds with first normal form.
2