How to get a value from a column with its name matching a value in another table

1

Hello everyone,

I’m trying to find a way of dynamically select a column in a table from the value in an another table. In table tb-StaffSalary I want to get the value for annual salary that matches the level and step of each person in that table (by rows). The values are stored in a table that looks like a matrix, Level in one column, then each step (1 to 10) in different columns. I’m not sure this is the most efficient data scheme though and I am open to solutions to make this more efficient. Link to the test document below.
Many thanks in advance for your insights and help.

2

Hi Gerardo,

You cannot achieve what you want with the way you structured the data.

My recommendation would be that instead of a matrix you would store the data in a tabular form.

image
image623×396 20.9 KB

You can still display it as a matrix with groups and now your issue becomes trivial to solve.

image
image1317×585 54.5 KB

I’ve created a new page in your document with the two tables above.

Hope this helps,

Pablo

3

Gracias Pablo.
I reached the same conclusion about the schema and adopted the approach you proposed. It works. Many thanks for taking the time.
Cheers