Help! How to pull information from last updated column?

Kerry Speirs

Hey everyone,

Hoping someone can help me figure this out!

We have a sheet we are using to track weekly maintenance on our machinery. There is a parent row with the machine name and the actual checks are the collapsible rows underneath. The checks are filled out by operators on another report/form so this sheet gives the supervisors an overview of the year of all the checks that have been carried out.

When the checks are completed, the operator types in the service hours and selects "All Checks Completed". This check box triggers a formula which automatically generates the date it was completed at the top row beside the machine name, see screenshot.

That all works fine for us however we are creating a new sheet to keep onto of the servicing requirements for the machinery as well and to save double working/handling, we wanted to pull the last inputted maintenance check date and corresponding service hours over.

I have been trying to work this out myself just on helper columns at the side of this sheet but haven't quite got it. Assumed it would be some combination using COLLECT, MAX, INDEX or JOIN but I haven't managed to get it to work.

Any help or advice would be greatly appreciated!

KPH

The original JOIN was:

=JOIN([Week 1]@row:[Week 52]@row, "")

This will join all the dates. But, as you spotted, will also join the - as it is created by your formula in the parent row.

There could be any number of - to the right of the last date which will mean your date is no longer the 8 characters to the right hand end of your string.

To strip them out we can use a SUBSTITUTE function, changing "-" to "". The syntax is:

=SUBSTITUTE([column with text in]@row, "-", "")

Rather than creating additional helper columns, you can do this all in one by putting the JOIN function into the SUBSTITUTE function instead of the column reference:

=SUBSTITUTE(JOIN([Week 1]@row:[Week 52]@row, ""), "-", "")

You then pop that into the formula for step 2, to extract just the 8 characters at the RIGHT, this:

=RIGHT(JOIN([Week 1]@row:[Week 52]@row, ""), 8)

Becomes

=RIGHT(SUBSTITUTE(JOIN([Week 1]@row:[Week 52]@row, ""), "-", ""), 8)

And when combined with the IF to make this only display on parent rows, you end up with:

=IF((COUNT(CHILDREN([Plant Maintenance Details]@row))) < 1, "", RIGHT(SUBSTITUTE(JOIN([Week 1]@row:[Week 52]@row, ""), "-", ""), 8))

KPH

Hi @Kerry Speirs

I have two questions.

1. Would it be possible to see another example with an extra column to confirm what the input looks like and how that should be handled?

For this example, I think you want 125 and 8/11/23 in your new log. If there is another column, with say 200 service hours in it, but the All Checks Completed box was checked on 7/9/23, what do you want then - still 125 and 8/11/23?

2. Does the sheet have other machine names below, with child tasks and "Service Hours" ? And if so, do you want one row per machine name in your new sheet? Like this:

KPH

And, as I've been thinking on the solution, another 2 questions, to make sure what I'm thinking will work:

3. Is the most recent date always going to be in the furthest right column?

4. Are your dates ddmm or mmdd?

Kerry Speirs

Hi @KPH

Thanks for your replies! I have hopefully answered your questions below:

1. I have added a screenshot below showing more input data. No, I don't want record every input of service hours, just the most recent.
2. The sheet does have more machines set up the same with parent row and child tasks, not all have service hour requirements.
3. Yes, the sheet is set up to the tick boxes correspond to each calendar week so the the most recent date will always be the furthest column on the right.
4. dates are ddmm

Thanks in advance!

KPH

Your screen shot did not appear so I have made some assumptions and made up my own equipment list. I hope this is close enough to yours to make this understandable. I call this Sheet 1.

Here is a suggestion

Step 1 Create a list of machines on sheet 2

Essentially all you need is to make sure the list on sheet 2 exactly matches the list on sheet 1. If you want to create this list using formula, we can, but that could be overkill if you only have a short list.

Note: We can’t use the hierarchy-based functions across sheets so indexing a list of ancestors on a separate sheet isn’t going to work for us. Instead, we need to add a helper column to the initial sheet to identify the rows that are machine names. You can then use this in the cross sheet reference (and hide it in the original sheet). We can come back to this if necessary. 

Step 2 Determine the latest date

MAX is not going to be easy to use on your dates because they are not in date format. But we can use the fact that the most recent is the one on the right. So, we first JOIN all the columns together and then use the RIGHT function to retain only the final 8 digits (which happens to be the date in the right most populated date column!). This formula will do that:

=RIGHT(JOIN([Week 1]@row:[Week 52]@row, ""), 8)

Important to note here: This only works if you stick to the date format where you use 01 instead of 1 – we are using position to figure out the latest date. If you can't do that we can use some delimiters instead.

Before we make this a column formula, we need to add a little more logic to ensure it only displays on the rows with the machine name in, otherwise we’ll have parts of the service hours included in this column which will mess up our next step. This formula counts how many children the row has and if it is less than 1 it puts nothing in the cell. If 1 or more, it puts in the joined and trimmed date.

=IF((COUNT(CHILDREN([Plant Maintenance Details]@row))) < 1,"", RIGHT(JOIN([Week 1]@row:[Week 52]@row, ""), 8))

Step 3 Bring the latest date into sheet 2

Now you can use INDEX and MATCH to find the value in the column you just added to sheet 1 where the machine name in sheet 1 matches that in sheet 2. You will need to use a cross sheet formula. Your sheet references will replace the parts in bold.

{Sheet 1 Latest Col} is the new column we added in step 2

{Sheet 1 Plant Maint Details} is your original column with the machine names and tasks in

=INDEX({Sheet 1 Latest Col}, MATCH([Machine Name]@row, {Sheet 1 Plant Maint Details}, 0))

Step 4 Convert that date into date format

You may or may not want the date in sheet 2 to be held in a date column so that you can do other analyses. If so, we can return to that. For now, I am leaving the date as text.

Step 5 Identify the service hours

We need to add a second helper column to sheet 1, that we can hide later.

I was hoping I could do an index match match to find the child row called service hours and the column where the parent row date matched the latest date. But I’ve got myself in knots. So now I am thinking we just join all the hours together and then extract the last one based on a delimiter.

But I have to stop now for today. I can pick this up later, if no on else jumps in to finish it.

KPH

This follows on from my comment above.

Step 5 Identify the service hours

We need to find the service hours for the most recent week from this:

If service hours was always the same number of rows below the parent, this would be easy. You could use a formula like this:

=INDEX([Week 1]6:[Week 52]6, 1, MATCH(Latest@row, [Week 1]@row:[Week 52]@row, 0))

But this only works if the Service Hours row is always the same number of rows below the parent, 5 in this example (as in the Bobcat and John Deere rows here). But when this isn't the case the formula won't work.

Option 1

One option is to move the Service Hours to always be the row below the parent. Then this will work (as you drag it to other parent rows the 2 increases to the row below the row the formula is on).

=INDEX([Week 1]2:[Week 52]2, 1, MATCH(Latest@row, [Week 1]@row:[Week 52]@row, 0))

Option 2

Another option is to add a helper row that has a formula to copy the hours into that row but lets the users still enter them as usual. This is a little messy though, especially if you have a lot of machines.

Option 3

The third option, and the one I'd like to figure out is to identify the child row called "Service Hours" and use that as the range in the INDEX. I can do it in excel but am stumped in Smartsheet.

Option 4

As I mentioned above, we could JOIN all the service hours with a delimiter and then remove the delimiters that have nothing after them, and then extract the last piece of text after the final remaining delimiter. This will need a combination of FIND, SUBSTITUTE and LEN and is not my area of expertise. You need to extract the last numerical data where the strong could like like any of these:

200|50|75

100||50

58|72|

61|21||

Kerry Speirs

Hi @KPH

Thanks for your help on this unfortunately I have fallen at just step two!

As the columns are set as checkboxes, I had put a formula in the top parent row that basically says if "All Completed Checks" is checked, then it shows todays date, other with it shows "-". This was just to remove the visual of a check box so an operator didn't select it and mess up the date formula. Because of this the Join/Right formula is joining all of the "-", see screenshot.

Any ideas on the best way to tackle this?

Thanks in advance!

KPH

Could you change the formula that you have in the parent row to show "" rather than "-"?

If not, can you use a SUBSTITUTE formula to remove the - after doing the JOIN but before doing the RIGHT.

Kerry Speirs

@KPH If I change the formula to " " then it just shows tick box rather than a blank cell. How am I best to use SUBSITITUTE?

Thanks in advance

KPH

The original JOIN was:

=JOIN([Week 1]@row:[Week 52]@row, "")

This will join all the dates. But, as you spotted, will also join the - as it is created by your formula in the parent row.

There could be any number of - to the right of the last date which will mean your date is no longer the 8 characters to the right hand end of your string.

To strip them out we can use a SUBSTITUTE function, changing "-" to "". The syntax is:

=SUBSTITUTE([column with text in]@row, "-", "")

Rather than creating additional helper columns, you can do this all in one by putting the JOIN function into the SUBSTITUTE function instead of the column reference:

=SUBSTITUTE(JOIN([Week 1]@row:[Week 52]@row, ""), "-", "")

You then pop that into the formula for step 2, to extract just the 8 characters at the RIGHT, this:

=RIGHT(JOIN([Week 1]@row:[Week 52]@row, ""), 8)

Becomes

=RIGHT(SUBSTITUTE(JOIN([Week 1]@row:[Week 52]@row, ""), "-", ""), 8)

And when combined with the IF to make this only display on parent rows, you end up with:

=IF((COUNT(CHILDREN([Plant Maintenance Details]@row))) < 1, "", RIGHT(SUBSTITUTE(JOIN([Week 1]@row:[Week 52]@row, ""), "-", ""), 8))

Kerry Speirs

Awesome, thanks so much for your help @KPH !