Trying to pull data from one sheet to another

I'm trying to get the most recent submission of a form (about vehicles) to pull the most recent mileage to our master vehicle sheet.

I have tried several things, and I can't figure it out.

=(JOIN(COLLECT({Most Recent Vehicles Check in/out Range 4}, {Most Recent Vehicles Check in/out Range 3}, HAS([ASSET#]@row,@row)),"-")

I have also tried:

=INDEX({Most Recent Vehicles Check in/out Range 4}, MATCH([ASSET#]@row, {Most Recent Vehicles Check in/out Range 4}, 1), 4)

I want the current mileage to be pulled from the below sheet.

Do you have any advice? There are no duplicates, and i've already verified everything is the most recent entry.

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

AravindGP

Hi,

I assume the first image is your destination sheet (Master Vehicle Sheet) and the second one is your sheet to collect mileage through a form. Few questions before I can give a formula, do you want the check in or check out milage to be pulled? Do you have a date or a running number (auto number) in the sheet with the mileage?

smartsheet_please

You assume correctly.

I do want check in or check out and mileage to be pulled. I don't have a date or an auto number yet.

AravindGP

Please add an auto number column, just as Seq (for sequence) as the name of the column. You can then use this formula.

=INDEX({Current Mileage: column reference}, MATCH(MAX(COLLECT({Seq column reference}, {Asset Number column reference}, [Asset#]@row)), {Seq column reference}, 0))

smartsheet_please

It didn't work, I may have missed something. I did exactly what you said above.

Paul Newcome

Since you already have a checkbox indicating the most recent, you shouldn't need any additional columns. You should be able to just use:

=INDEX(COLLECT({Column To Pull Over}, {Checkbox Column}, @cell = 1, {Asset Column}, @cell = [Asset#]@row), 1)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.