Dropdown list from a range?

[byslexic]

I’m trying to “nest” some Lookups or indexes, but getting a little lost at the 2nd step in my process.
This worksheet lists several companies, each of which has one or more of several different pay classes [Table 1]. Each pay class has 3 tiers [Table 2].

The desired functionality of this sheet is that

1. the user will select a company from a dropdown list,

2. which will then populate the next dropdown list with only the pay classes available for that company.

3. The user will then select a pay class from that dropdown list.

4. Lastly the user will select pay tier A, B, or C.

5. The worksheet will then display the pay rate in the correct class and tier.

A worksheet illustrating the desired product is attached, with the broken Step 2.

How can I use Step 1 to populate the dropdown in Step 2, (cell A5)?
Thank you in advance for any help.
Desired Output.ods (17.5 KB)

[Lupp]

What you want to achieve is basically database functionality. The validation tool of a spreadfsheet isn’t an actually satisfying surrogate.
You may have a look into the reworked example:
DesiredOutputImplemented.ods (23.7 KB)

[byslexic]

Thank you very much, that’s the formula I’ve been trying to accomplish, but couldn’t wrap my head around:
INDEX($Sheet1.$E$2:$L$7,MATCH($A$2,$D$2:$D$7,0))

I’m sure that my project’s worksheet would be much better with an integrated database, but I am limited to CALC only, for a few reasons I won’t go into.

Just curious why this solution ( being a data validation function) isn’t a fully satisfying surrogate?

[Villeroy]

Because a rectangle of cells is not a database table.
Because a column of cells in that rectangle is not a field.
Because a row of cells in that rectangle is not a record by any means.
Because the validation feature does not care about referencial integrity.
Because the validation feature is gone when you paste unvalidated cells (or differently validated cells) over validated cells.
… to be continued.