Anyone knows how to retrieve the latest entry of an item?

[Clement]

Hi all,

I am trying to find the latest status of a project using Arrayformula…

Gsheet structure :

• [Project] tab with all my ongoing projects (name {in column A}, launch date, comments…)

• [Status] tab which tracks the successive status of each project (project name {in column D}, status, date {in column G}, comments…)

Used formula :
I am trying a Max(If()) formula to retrieve the latest “status date” of a project (I will find a way to retrieve the related status with it) :
=ARRAYFORMULA(max(if(STATUS!D:D=PROJECT!A2,STATUS!G:G,0)))
It actually sends me back the last “status date” of the first project for all my projects

Any recommendation on what’s going wrong? I have unsuccessfully tried a Google search and I am out of options right now…

[Jeff_Hager]

Don’t ask how it works. I just know it works.

Display the latest item from my sheet

Yes, this formula returns the last item that matches a search value: =IF(ISERROR(ARRAYFORMULA(LOOKUP(2, 1/(PhotoChoiceResponses!A$2:A=A3), PhotoChoiceResponses!B$2:B))), "", ARRAYFORMULA(LOOKUP(2, 1/(PhotoChoiceResponses!A$2:A=A3), PhotoChoiceResponses!B$2:B))) This is what I use in the Photo Choice example in https://concepts.glideapp.io/ Here is a simplified version with the conversion from ( , ) to ( ; ) for you: =ARRAYFORMULA(LOOKUP(2; 1/(FormResponseSheet!A$2:A=A3); FormResponseSheet!B…

[Tim_Sullivan]

@Jeff_Hager

Don’t ask how it works. I just know it works.

I have lots of stuff like this that fall in this category.

[Clement]

Ok this one is really impressive and I think I won’t even try to understand it

“Curiously”, it works perfectly

However, I need the formula to replicate automatically to any new line (meaning any new project in my app) which can be created thanks to a “form button” in my app. This is why I was trying an Arrayformula. Can you confirm that the “Lookup(2,1/…)” formula won’t replicate?

Best,

[spencersRus]

Not sure if this helps or even does what you want, but it seems to be relevant here.

In this app: https://ktle5.glideapp.io

I have people fill out a form to change the version. at the bottom of the screen.

In the Spreadsheet
Tab Today - Q2 - has the formula - =index(Update!B:B,max(row(Update!B:B)*(Update!B:B<>"")))

It is picking up the LAST non-empty cell in that other Update sheet.

[Clement]

Thanks for the insight. I can’t copy thee app to check of the formula works, but I understand that you are able to duplicate the formula thanks to the form button?

[ionamol]

I don’t know if I’m missing something but I think that what you need is the same formular I’m using in my Slide OS.

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF( B2:B = “”, “”, VLOOKUP(B2:B, photos!B2:C, 2, False))), 3650, 1)

1. Array Constrain / Formula to replicated the Formula to every new column
2. IF to check if the referenced cell isn’t empty
3. B2:B > The reference value to check (eg: title for the selected photo)
4. photos!B2:C > Range to search for this value
5. 2 > Index of the result wanted

Let me know the result if you test it.

[Clement]

Hello @ionamol and thanks for the input! I’ll check if I can do something with Array_Constraint which I kind of forgot

Vlookup can’t work for me as I have 2 criterion for my “lookup” : the project name and the latest date.

Maybe I could use a sort formula in order to sort in “date descending order” all the project status, and then use your Vlookup formula… I’ll let you know if I succeed by tomorrow (kids are back from school! )

[Jeff_Hager]

@ionamol Doesn’t array contraint get thrown off if you have empty rows? I thought I read somewhere that empty rows caused issues, but maybe it had to do with the type of formula used inside of it. I’ve never used it, but I’ll have to play around with it sometime.

@Clement I tried what you mentioned and it looks like you can use a formula like this =ARRAYFORMULA(VLOOKUP(A2:A, SORT(A2:C, C2:C, false), 3, false)) . What it’s doing is using the SORT to sort the data by date in decending order. Then uses the VLOOKUP to return the first record that matches the project name (first match will be last date for that project because of the sort).

[Clement]

That would be awesome for me. I did not have the opportunity to test it so far. I will let you know if I succeed

Le ven. 31 janv. 2020 à 04:19, Jeff Hager via Glide Community glideapps@discoursemail.com a écrit :

[Clement]

it works !
It just required to switch the colums in order to have the “date” column between the “project ID” and the “satus” ones
Many thanks for all your inputs

[Carlos_DeSilva]

It’s a good thing you found the Glide option, since it would be much quicker than a spreadsheet formula. That was gonna be my suggestion.

[Clement]

@Carlos_DeSilva : I did use a spreadsheet formula… Do you have a “Glide embedded” solution in mind?