Newbie - Tracking Item location based on most current date
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
.
Best Answers
-
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.
-
That worked perfectly!!
Thank you for taking the time to walk me through this.
Answers
-
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))
-
Will this only show only the most current record for a cylinder based on the date it was filled?
-
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.
-
I am sorry but I am not following your formula.
-
Which bit do you need help with?
-
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.
-
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.
-
That worked perfectly!!
Thank you for taking the time to walk me through this.
-
Happy to help. 👍️
-
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?
-
@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.
-
@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.
-
@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"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!