Forum Discussion
Solved
Find latest iteration of reference number
Hi all, Hoping you can help. I have a spreadsheet that tracks document reviews. I have been asked if there is a way we can track the documents that have had an initial review and then sent ...
Can you rely upon the Iteration numbers being in increasing order down the sheet? If so, you can search for the final occurrence of each DRN and return the corresponding Category. I normally use a dynamic array to produce the results for the each record but, since you are using a table, the formula could be placed in the first record and it will propagate down the table.
= LET( category, XLOOKUP([@[DRN Number]], [DRN Number], [DRN Category],,,-1), IF(category=1, "closed", "accepted") )See the right-most field. If you have to check the order of the Iteration numbers, the formula would get more complicated.
BTW, it is best practice when using Tables not to allow empty records within the list or at the end of the table. The Table should grow automatically as data is added.
=IF(AND(XLOOKUP($A13,' FR-104-RCB Checker_Info Board'!$A$10:$A$3300,' FR-104-RCB Checker_Info Board'!$A$10:$A$3300),XLOOKUP($A13,'FR-105-RCB Checker_MEFA '!$A$9:$A$3300,'FR-105-RCB Checker_MEFA '!$A$9:$A$3300)),XLOOKUP($A13,'FR-105-RCB Checker_MEFA '!$A$9:$A$3300,'FR-105-RCB Checker_MEFA '!$B$9:$C$3300,0),XLOOKUP($A13,' FR-104-RCB Checker_Info Board'!$A$10:$A$3300,' FR-104-RCB Checker_Info Board'!$B$10:$C$3300,0))