I was not sure how to word my problem to search for a solution, so will try and describe it here using simplified tables.

I have created two tables, Table A and Table B.

Table A can be thought of as a master table. It is a list of water bores H1-H13, each with a hole elevation (height above ground level)

Table B is a table that is used by people to enter values for depth to water level.

A person in the field may measure the water level in bore H10 first. They will select that from a dropdown box in Table B in column D (HoleID).

Then the current date (and maybe time) will be populated in column E.

They will then take a measurement with a water level dipper that will indicate a distance to the water level, eg. 10 (for 10m)

The WL Elevation column (G) will then automatically get populated via a formula: Hole Elevation - WL

eg. for H10, if 10 is entered then the calculation 353.86-10 will be executed.

My issue arises because the order in which a water bore water level is measured can change and therefore a simple calculation will not work. Table A is fixed (at least until a new water bore is added or removed) but Table B will go on indefinitely whereby the same water bore might appear hundreds of times over many years.

Is there a straightforward way to make this link? i.e. if someone selects a particular HoleID in table B to make their measurement, only the corresponding HoleID Hole Elevation in Table A will be used to make the WL Elevation calculation?

Any assistance would be greatly appreciated.

Luke