I am having trouble with the expression formula returning a value in the form when I run the form. Here’s the format of the table “Mileage” that I am running the LOOKUP as an Initial Value formula (where Date is the Key). These are the relevant column headers in the table:
Date - Starting miles - Current miles
The goal is to have the form pre-fill the new fueling stop’s dialog box with the last fueling stop’s Current miles data as the NEW fueling stop entry’s Starting miles dialog box. This should be editable in case one fueling event was missed along the way and needs to be added manually.
Here is the format of the formula I have adapted based on the one you provided - I have spent a while studying the structure and believe I understand each of the arguments in the process. I am putting this formula in the “Initial Value” formula box on the “Starting miles” data line in the data editor.
My problem is that while the formula gets “accepted” by the verification process, it does not return the value into the “Starting miles” dialog box when I go to add a new fill up. Here’s the formula as I have adapted it:
LOOKUP(MAXROW(“Mileage”,“Current miles”,[Current miles]=[_THISROW].[Current miles]),“Mileage”,“Current miles",“Current miles”)
If I understand the syntax of this correctly (which evidently I don’t), this should tell the LOOKUP command to:
Find the row in the Mileage data sheet with the maximum value from the column “Current miles” and copy the data in the Current miles column from that row and place it in the data set “Mileage”, in the column “Starting miles” since that is the place the LOOKUP formula is located in the Initial Value spot.
I based that assumption on reading this formula for the LOOKUP() function:
LOOKUP(find-value, in-dataset, in-column, return-column)
Where the “Find-Value” is MAXROW(Table, Column, reference)
Attached is a screenshot of the spreadsheet. The formula I am working with is above.
Is the _THISROW part of this equation referencing the new line in the spreadsheet (in which case the line would be blank thus returning a blank value) or is it referencing the line found with MAXROW(). I think it is the latter, but since the new row is blank might be what I am seeing: a blank return.
Am I interpreting the return-column argument correctly: It is the location for the value of the identified search - in this case the “starting miles” of the new dialog box.
Sorry for the long post question. I am trying to learn the program. Any suggestions on a video series or other aids I can use to learn on my own would be appreciated!