Updating Previous and Latest checkboxes based on a value in another column.

A_C

Hi & Happy Friday,

I'm putting together a status reporting sheet and I need to be able to update the previous and latest rows, based on a separate column, in this case the Submission Status column.

Formula in the Previous column is:

=IF(([HC-Row ID]@row + 1) = MAX([HC-Row ID]:[HC-Row ID]), 1)

Formula in the Latest column is:

=IF(([HC-Row ID]@row) = MAX([HC-Row ID]:[HC-Row ID]), 1)

I can either use the Created Date column or as I have been trying, use a Row ID based method.

What I want to do is, for the Previous and Latest columns, only include the Submission Status of "5-Approved" as the criteria for the formula. I just can't seem to get it to work, tried various variations of formulae, the formula generator, and the community info. I know this is probably very simple but, not for me today :-)

So for the Previous column., it would be something like:

IF([Submission Status]@row = "5-Approved" then (using the row ID method)

=IF(([HC-Row ID]@row + 1) = MAX([HC-Row ID]:[HC-Row ID]), 1)

For the Latest column, it would be something like:

IF([Submission Status]@row = "5-Approved" then (using the row ID method)

=IF(([HC-Row ID]@row) = MAX([HC-Row ID]:[HC-Row ID]), 1)

Or would it be best / better practice to use the Created column, rather than the Row Id based method.

As ever, any help would be great.

A_C

…sorry meant to add

HC-ID is an autonum column.

HC-Row ID formula is:

=MATCH([HC-ID]@row, [HC-ID]:[HC-ID], 0)

Georgie

Hi @A_C,

It looks like you’re almost there! You’d just need to turn your existing IF formulas into nested IFs. 

So, for the Previous column, your formula would be:

• =IF([Submission Status]@row = "5-Approved", IF(([HC-Row ID]@row + 1) = MAX([HC-Row ID]:[HC-Row ID]), 1))
  

And for the Latest column, your formula would be: 

• =IF([Submission Status]@row = "5-Approved", IF([HC-Row ID]@row = MAX([HC-Row ID]:[HC-Row ID]), 1))

Whether you use the Created Date column or Row ID column is up to you. If there’s any chance of rows being added within the same minute on the same day, I’d suggest using the Row ID to ensure that the value in the column will always be unique for each row, but otherwise you can decide which column to use within your formulas.

Hope that helps!

Georgie

A_C

Hi Georgie,

Thanks for your help with this, I made the changes and it worked great on the master sheet.

I then was asked to have a separate sheet to generate an update request.

However, I have found in the testing an issue with using the number based method.

A bit of process background.

1 - I've used automations to copy the latest row to a separate sheet.

2 - In the separate sheet once the row arrives, the "Submission Status" is updated using a workflow to be "1-Update Required", this kicks off an update request and the user updates the info and changes the Submission Status to "2-Updated".

3 - This then moves the updated row back into the main sheet, to be reviewed and approved etc.

This process, messes up the HC-ID (Autonum) and the HC-Row ID columns, the numbering gets out of sync. It also seems to be the same with the Created column as well. The created in the 2nd sheet has the same date / time as the 1st sheet, so when I move it back from the 2nd sheet to the 1st sheet, I have 2 rows in the 1st sheet with the same created date / time. I did think of using the modified date, but changes may be made to the data and would throw the things out of sync, so I'll use the Report Date col.

So I'm now looking at using the Report Date column to find the latest and previous rows. When I use the formulae below, I can't get it to work.

=IF([Submission Status]@row = "5-Approved", IF(([Report Date]@row + 1) = MAX([Report Date]:[Report Date]), 1))

I'm thinking it is because simply taking 1 off the Report Date won't work in this case as the reporting will be monthly, (in fact some will also be weekly, quarterly, half-yearly and annually) - but I'll get to that once the monthly one is working :-), I may also need MAX/COLLECT, but I'm not sure.

I'm thinking something like:

For the Latest col (this dosen't work, but hopefully I'm on the right path) - I currently get incorrect argument set.

=IF([Submission Status]@row = "5-Approved", IF([Report Date]@row = MAX(COLLECT([Report Date]:[Report Date], 1), 0))

For the previous one, I'm thinking to take a month off the latest date found, and flag that row up. I'm stuck on that one as well I'm afraid.

Any additional help you can provide would be great.

Georgie

Hi @A_C,

To use the Report Date column instead, you can use the following formula for your Latest column:

• =IF([Submission Status]@row = "5-Approved", IF([Report Date]@row = MAX([Report Date]:[Report Date]), 1))

For the Previous column, I’d use the LARGE function with 2 to return the 2nd latest date in the Report Date column when the submission status is Approved:

• =IF([Submission Status]@row = "5-Approved", IF([Report Date]@row = LARGE([Report Date]:[Report Date], 2), 1, 0))

Would that work?

Georgie