Newbie - Tracking Item location based on most current date

Troy Bjelland

I am fairly new to Smartsheet's and am struggling to figure out some of the formulas. I am with a volunteer fire department and we are using Smartsheet's to track our equipment and maintenance. I currently have a sheet that tracks when we fill our air cylinders and which station the cylinders are at. Each cylinder which has numerous entries and will change from station to station. I would like a separate sheet in card view for each station showing their bottle inventory. The problem is when we fill Cylinder 15 twice it shows two cylinder 15's at that station. I believe i need to use max(collect) but I don't understand how to make it work.

Any help would be much appreciated

Bottle Tracking.png

Service Log.png

.

Paul Newcome

Using the proper method to set up cross sheet references, your formula would look something like this...

=INDEX({Cylinder Log - Cylinder Location Column}, MATCH([Cylinder ID]@row, {Cylinder Log - Cylinder ID Column}, 0))

You are going to need the cylinder ID populated in the second sheet to be able to match on.

Troy Bjelland

That worked perfectly!!

Thank you for taking the time to walk me through this.

Paul Newcome

If you are using a form to fill out the log, you can set the form to populate at the top row then use an INDEX/MATCH combo (instead of VLOOKUP).

=INDEX({Cylinder Log Location Column}, MATCH([Cylinder ID]@row, {Cylinder Log Cylinder ID Column}, 0))

Troy Bjelland

Will this only show only the most current record for a cylinder based on the date it was filled?

Paul Newcome

Yes. The MATCH will stop at the first match and works its way from top to bottom when evaluating a single column. Since the form is populating new entries at the top of the sheet, the most recent entry will always be the first match.

Troy Bjelland

I am sorry but I am not following your formula.

Paul Newcome

Which bit do you need help with?

Troy Bjelland

Maybe i need to go back a bit. My apologies and this is very new to me.

I have the cylinder log sheet (see attached) , and I want to transfer the data to another sheet named Cylinder Track which I can use in card view to show the latest location of the cylinder based on the Cylinder log sheet. I don't know where to start. I can even get the @row to show up as your formula shows.

I want it to look like this with only the most current service showing in the correct location.

Sorry for so many questions. I am finding Smartsheet has very little online support unless you want to pay $2500 for 10 hours.

Thank for your expertise and time.

Paul Newcome

Using the proper method to set up cross sheet references, your formula would look something like this...

=INDEX({Cylinder Log - Cylinder Location Column}, MATCH([Cylinder ID]@row, {Cylinder Log - Cylinder ID Column}, 0))

You are going to need the cylinder ID populated in the second sheet to be able to match on.

Troy Bjelland

That worked perfectly!!

Thank you for taking the time to walk me through this.

Paul Newcome

Happy to help. 👍️

Summer Edwards

Hi @Paul Newcome - Wondering if an index match formula works to pull values across sheets based on matching values and most current date if the source sheet is not sorted newest on top?

Paul Newcome

@Summer Edwards It would depend on your exact structure, but typically you would need to include a helper column on the target sheet with a MAX/COLLECT to pull in the most recent date for that line item then an INDEX/COLLECT referencing the date helper to pull in the most recent entry.

Summer Edwards

@Paul Newcome Ok, thank you. What would an example of the helper column look like where you have a date column and a value column. Value names are repeated with different dates.

Paul Newcome

@Summer Edwards You would use a MAX/COLLECT to pull in the most recent date first.

=MAX(COLLECT({range to collect}, {1st criteria range}, 1st criteria))

=MAX(COLLECT({date range}, {value range}, "value"))