[Solved] Connect a formula from one table to another problem

[lukeh81]

Hi,
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

 

[Aniruddah Alam]

Hello Lukeh81,

Based on your description, I have created a file that contains the formula that you might be looking for. To be specific, I have used a formula in the G column, which looks like this:

=VLOOKUP([@HoleID],Table1,2,FALSE)-[@WL]

Table1 refers to the Table A in your file. You can modify the reference as per your requirements.

Furthermore, in Table B, I have made the data validation of the HoleID column dynamic. So, whenever you add a new HoleID in Table A, you will see the new entry automatically in Table B. I have used the following formula in the Data Validation list source:

=INDIRECT("Table1[HoleID]")

You can replace the term Table1 with the actual name of the Table A in your Excel file.

Moreover, I have added the IFNA function to handle the #NA error for better appearance.

=IFNA(VLOOKUP([@HoleID],Table1,2,FALSE)-[@WL],"No Data Found")

I have also prepared a sample illustration to show you how it works.


I also added the file that I worked with which you can download and use it directly.

Regards

 

[lukeh81]

Hi Aniruddah,

Thank you, this is brilliant! I had been toiling away at this, and wasting valuable work time, for far too long.

Much appreciated,

Luke

 

[lukeh81]

Hi,

This might be asking too much, so if you or someone else here is unable to help, I would understand.

The final structural concept of my water level measurements in Excel field sheet and database, is shown in the spreadsheet I've attached below. This is a highly simplified version and I hope it makes sense the way I have presented and explained it.

The main difference to the previous one I shared, is that now there is a third table called the TOC table which has data for each hole that corresponds to the height above the ground surface to the top of casing where an individual would take a water level measurement from in the field. Unfortunately, this can change over time and thus the final calculated readings and historically calculated readings will change if there is only one TOC distance for each hole and this changes.

Again, any help or direction would be greatly appreciated. I would love to learn this stuff myself, but just can't find the time, but have learned a lot about what Excel can do from this forum.

Cheers,

Luke

 

[Aniruddah Alam]

Hi,

This might be asking too much, so if you or someone else here is unable to help, I would understand.

The final structural concept of my water level measurements in Excel field sheet and database, is shown in the spreadsheet I've attached below. This is a highly simplified version and I hope it makes sense the way I have presented and explained it.

The main difference to the previous one I shared, is that now there is a third table called the TOC table which has data for each hole that corresponds to the height above the ground surface to the top of casing where an individual would take a water level measurement from in the field. Unfortunately, this can change over time and thus the final calculated readings and historically calculated readings will change if there is only one TOC distance for each hole and this changes.

Again, any help or direction would be greatly appreciated. I would love to learn this stuff myself, but just can't find the time, but have learned a lot about what Excel can do from this forum.

Cheers,

Luke

Hi Luke,
Thanks for reaching out again. Sorry for my late reply.

Based on your new requirements, I have created 2 new User Defined Functions named GetmAGL and Get_PreviousData. Below is their descriptions:

GetmAGL(HoleID As Range, Date_Measured As Range, Excel_table As Range):
It takes three arguments: a range representing HoleID, a range representing Date_Measured, and a range representing an Excel table.

Functionality:

Matching HoleID: It checks if the HoleID provided matches any values in the first column of the Excel table.
Date Range: It verifies if the Date_Measured falls within the range specified by the Start Date and End Date columns in the Excel table. If the End Date is empty, only the condition Start Date <= Date_Measured is considered.
Extracting Data: If both conditions are met, it retrieves the corresponding value from the TOC mAGL column in the Excel table.

Get_PreviousData(HoleID As Range, Excel_table As Range):

It accepts two arguments: a range representing HoleID and a range representing an Excel table.

Functionality:

Searching for Previous Data: The function iterates from the row above the active cell to the top row of the first column of the Excel table (in your case the table name is "Measure"). It checks if the HoleID provided matches any values in the first column of the Excel table.
Extracting Data: If a match is found, it retrieves the corresponding value from the third column of the Excel table. If it doesn't, it returns "-".

I have also included an event-driven macro that will insert the current date on the 2nd column of the Measure table whenever any non-empty value is entered on the 1st column.

Here is an illustration for you:



Kindly let me know if this is what you are looking for.

Regards