Oct 24 2023 12:27 AM
Oct 24 2023 12:27 AM
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 back to our customer to make changes etc.
These reviews are assigned a reference number in column H and the different iterations of this in column I. So one reference number can appear multiple times.
So I need a formula to look at the reference number then find the latest iteration then look in column AK to check the category if its reached a 1 then I need it to mark all version of that reference as accepted and if there's a 0. 2. or 3 then I need it to return Closed.
Not sure if this can be done in one formula or if another will be needed to first find the latest iteration and then another for the Accepted or Closed.
Hope this all makes sense.
Oct 24 2023 09:14 AM
To achieve this in Excel, you can use a combination of functions and helper columns. It's often easier to break down complex tasks into smaller steps. Here's a step-by-step guide on how to accomplish your task:
=MAXIFS($I$2:$I$100, $H$2:$H$100, $H2)
This formula finds the maximum iteration value for the current reference number in cell H2.
=IF(AND(AK2=1, J2>0), "Accepted", "Closed")
This formula checks if the value in column AK is 1 and if the Latest Iteration (from the helper column) is greater than 0. If both conditions are met, it returns "Accepted"; otherwise, it returns "Closed."
Your spreadsheet should now have a column (K) that indicates whether each document is "Accepted" or "Closed" based on the criteria you provided.
The use of helper columns makes the logic easier to manage and understand. If you prefer a single formula, you can use nested IF functions, but it can become complex and harder to troubleshoot. The helper column approach is more transparent and easier to maintain. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Oct 24 2023 12:10 PMSolution
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.
Oct 24 2023 12:11 PM
Nov 01 2023 07:47 AM - edited Nov 01 2023 07:48 AM
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.
Nov 01 2023 07:49 AM - edited Nov 01 2023 07:50 AM
Thanks for this it's helped me and I'm using some of what you suggested to work with some of the other suggestions posted