nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Sheet formulas not updating from Data Shuttle file

L. Barrett

Good morning,

I have a document that I download from an outside source. I have set up a data shuttle to import it into Smartsheet. I have other sheets that need to be updated based on the info in the imported sheet. None of the formulas are working once a new file is imported.

Details:

Sheet imported through Data Shuttle has travel information.

Other sheets were created to track travel expenses for each group. Each sheet includes Name, Dates, and Locations for that group. Formulas were set up to pull various fields from the imported sheet if the name, date, and location match. They all worked when set up but when I ran the data shuttle again with an updated file, none of the formulas are pulling data. All of the fields are blank.

Here is one of the formulas that should pull from the imported file:

=IFERROR(INDEX(COLLECT({Form ID}, {Departure Date}, @cell >= [One Day Before Start]@row, {Return Date}, @cell <= [Meeting End Date]@row, {Destination}, CONTAINS(City@row, @cell ), {Traveler Name}, CONTAINS([Traveler Last Name]@row, @cell )), 1), "")

I confirmed that the data that should appear exists on the import file.

Am I doing something incorrectly?

Thanks,

Laura

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Start by removing the IFERROR to see if it is masking an error or if the formula is actually pulling in a blank.

=INDEX(COLLECT({Form ID}, {Departure Date}, @cell >= [One Day Before Start]@row, {Return Date}, @cell <= [Meeting End Date]@row, {Destination}, CONTAINS(City@row, @cell), {Traveler Name}, CONTAINS([Traveler Last Name]@row, @cell)), 1)

L. Barrett

That returns an #INVALID VALUE Error.

Paul Newcome

That is essentially saying there is no match.

Are you able to provide a screenshot of a row that should have it's form ID brought over? Both the source data that should be coming over and the target sheet with the formula in it that shows each of the data points you are matching on?

L. Barrett

Here is the source sheet:

Here is the destination:

L. Barrett

Both of them come in on the upload. They are both Date columns.

Paul Newcome

Try a helper column to extract just the date. It will need to e a date type column and will use this column formula:

=DATE(VALUE(MID([Departure Date]@row, FIND(" ", [Departure Date]@row) - 4, 4)), VALUE(LEFT([Departure Date]@row, FIND("/", [Departure Date]@row) - 1)), VALUE(MID([Departure Date]@row, FIND("/", [Departure Date]@row) + 1, FIND("/", [Departure Date]@row, FIND("/", [Departure Date]@row) + 1) - (FIND("/", [Departure Date]@row) + 1))))

Use this same formula in another date type helper column to extract just the date form the [Return Date] column then reference these two helper columns in the INDEX/COLLECT.

L. Barrett

How do I add that to the imported file? Do I need to manually add it after every upload?

Paul Newcome

No. You would just add it to the sheet that is being populated by the Data Shuttle and make sure to apply them as column formulas. It should just be a one-time thing if the Data Shuttle is constantly updating the same sheet.

L. Barrett

Yesterday I was finally able to circle back to this project. I added the helper columns and am still receiving the #INVALID VALUE error.

Here is the formula with the helper columns.

=INDEX(COLLECT({Form ID}, {DoD}, @cell >= [One Day Before Start]@row, {DoR}, @cell <= [Meeting End Date]@row, {Destination}, CONTAINS(City@row, @cell ), {Traveler Name}, CONTAINS([Traveler Last Name]@row, @cell )), 1)

Any other ideas?

James Harris

The first thing I'd check is whether the helper column that contains Paul's formula is specified as a Date column, because you will get #INVALID VALUE in the helper column if it's not.

Generally speaking, it's always worth checking for things like trailing spaces in your imported data, just in case it's not matching because of a single space (particularly after the last character). Quite often, if the data was originally pasted in from another source, extra spaces at the start or end of the string can occur - and can be very hard to spot just by looking at the sheet.

L. Barrett

I confirmed that all of the date columns are marked as dates. I checked for spaces in the Form ID but all were clean. The criteria that I used does not require an exact match.

=INDEX(COLLECT({Form ID}, {DoD}, @cell >= [One Day Before Start]@row, {DoR}, @cell <= [Meeting End Date]@row, {Destination}, CONTAINS(City@row, @cell ), {Traveler Name}, CONTAINS([Traveler Last Name]@row, @cell )), 1)

The first upload worked fine. All of the formulas pulled the correct values. It appeared as an issue when I reloaded the upload document.

Any other ideas or suggestions?

James Harris

I'm wondering whether different character sets might be involved here.

Do you happen to have a sheet with the original import? If you have the same data on some rows in the original and in the new (not working) import, it would be interesting to run a comparison on some of those cells. i.e. any cells that look to be the same in the original and new imports. If you could have a checkbox formula in another column that is checked if Smartsheet deems the contents of the two cells to be identical, you'll be able to see if any hard-to-spot character set issues are at play here. The comparison checkbox formula would be something like: "=if([Original Cell]@row = [New Cell]@row, 1, 0)".

Thanks,

James

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.