I am new to spreadsheets!
I have created a google form to track a service technician's daily time record. The technician might visit multiple sites in a day. As Forms does not allow looping questions, I have had to create a number of duplicate sections. The Google Sheet attached to the form is here: https://docs.google.com/spreadsheets/d/1zzivoIt2gfrb21mRpE2sbiZA37hc-sLUYgDT74qcf6Q/edit?usp=sharing
I am wondering if anyone can explain to me how to build a formula that will give me the "Daily Finish" time ("Processed Data" Sheet, Column "AS") from the last recorded "FINISH" time in the row?
I have tried using XLOOKUP but that only gives me the data from either the first or last "Finish" column, not the last "Finish" column with data, ignoring blank cells
You may try:
=byrow(A2:AP,lambda(Σ,choosecols(ifna(filter(Σ,A1:AP1="Finish",Σ<>"")),-1)))
logic:
A2:P2
row) is designated as Σ, you can also change Σ to something of your choicefilter(Σ,A1:AP1="Finish",Σ<>"")
Here I am filtering the specific row where the header (in A1:P1) is Finish & at the same time I am also disregarding the cells with blanks in the rowchoosecols(.....,-1)
In the last step I am picking the last non-blank value from the filter output obtained in step 2