Updating one sheet from another

compliance_talfm1

Hi All, I'm not terribly savvy with formulas beyond sums… hoping someone can help :)

I have one sheet that tracks last major services for all our fleet - lets call this sheet "fleet"

I have another sheet that is comprised of forms completed by our mechanics (a checklist of all service components) - lets call this sheet "Services"

I would like for the date column on the "services" sheet to update the date of last service column on the "fleet" sheet.

This would obviously have to cross reference the registrations of the vehicles as well.

I have attached screenshots of the sheets if that helps.

vehicle list.PNG

service forms.PNG

kowal

hi @compliance_talfm1,

if both sheets have some unique number that can identify rows in both sheets you can go to Table View and use Link Column Data option (it's on the right in tools).

https://help.smartsheet.com/articles/2483494-manage-column-links

you do not need to use formulas for that.

Andrée Starå

https://community.smartsheet.com/discussion/139698/updating-one-sheet-from-another

Hi,

I hope you're well and safe!

To add to Tomasz's excellent advice/answer.

If you don't have access to the Table View, you can use a combination of INDEX/MATCH instead.

In any case, you can use the VIN to connect them.

More info.

Here's the structure for an INDEX/MATCH combination.

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0)

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row ID on as many rows as you need in the Destination sheet.

Update because I reread the post and noticed that I missed what Paul mentioned. I agree with him, use MAX COLLECT instead.

https://help.smartsheet.com/function/max

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

Paul Newcome

My suggestion would be a MAX/COLLECT.

=MAX(COLLECT({Form Sheet Date Column}, {Form Sheet Registration Column}, @cell = Registration@row))

This way you don't have to worry about whether new forms are at the top or bottom of the sheet or if the form sheet gets sorted for any reason.

compliance_talfm1

I tried the MAX/COLLECT but it doesn't seem to be working, have I entered it correctly?

Paul Newcome

Why do you say it isn't working?

kowal

hi @Andrée Starå,

I thought few weeks back the Table View came out of EAP and is avialable to everyone now.

Are there limitations with access I am not aware of?

compliance_talfm1

@Paul Newcome because it's not filling any date on the fleet sheet, I went in and refreshed the sheet, even went as far as logging out and logging back in, but nothing has changed - I can see there's a formula on the page (it has the little triangles in the cells) but the date did not transpose.

Paul Newcome

Double check that all columns are set as date type columns. If they already are, how are the original dates being entered?

compliance_talfm1

@Paul Newcome Original dates are being entered as a date (from the calendar selection) on a form. All columns (except the registration columns) are date fields. Can you use multiple formulas - like a vlookup and the max collect?

Paul Newcome

Yes. Multiple formulas can be used.

As a quick test… What does this give you?

=COUNTIFS({Form Sheet Registration Column}, @cell = Registration@row)