Forum Discussion
Find latest iteration of reference number
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.
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.
Hi Peter,
Thanks for the feedback. I've been playing about with a few things to try and get the Closed or Accepted to only appear on the latest DRN iteration row. I've got this work using a cell range Column BB, but when I try to use structured references, it doesn't seem to work BA.
My reasoning is that I want to calculate any DRN with our client that hasn't been returned based on the latest version we sent them. Just wondering if I'm missing something simple. I'm having the same issue with column AY it works with Cell ranges but not structured references.