Weird Cross Sheet Reference Issue

OK, so I have three sheets,

A job sheet
A cost sheet
An quote data sheet (which imports from Xero via a Zap).

In the job and cost sheet I'm interested in recording the quote no. In the quote number column for the job and cost sheets I have these formulas respectively....

=IFERROR(INDEX({DATA_QuoteNo},MATCH([DATA_ID]@row,{DATA_ID},0)),"No Quote")

=IFERROR(INDEX({JOB_QuoteNo},MATCH([JOB_ID]@row,{JOB_ID},0)),"No Quote")

Here is weird part.... the first formula correctly returns quote numbers, but the second formula returns "No Quote"... thinking I had made some sort of formula error I reduced the formula to...

=INDEX({JOB_QuoteNo},MATCH([JOB_ID]@row,{JOB_ID},0))

But the outcome was "No Quote", meaning that the cost sheet reads the job sheet cell as "No Quote" despite all columns being populated with actual quote numbers... weird.

Anyone have any ideas?

Find more posts tagged with

Accepted answers

All comments

Scott Peters

Hi @BullandKhmer - Without seeing a sample of the data it is hard to say exactly what the trouble might be, but one troubleshooting step I always recommend is to right-click on any cell in the sheet where you're building the formula, and select "Manage References", and then inspect each of them to make sure your reference is applied to the correct columns (and the entire column, not just some specific cells)

Genevieve P.

I agree with Scott!

From your description, the first thing I would do is check where your first reference is pointing:

=INDEX({JOB_QuoteNo}, MATCH([JOB_ID]@row,{JOB_ID},0))

If there were no matches, I would expect a NO MATCH error, not the "No Quote" value coming through. Are there any Job IDs that have "No Quote" associated with them?

I agree that it would be helpful to see the source data, but please block out sensitive data.

Cheers,

Genevieve

BullandKhmer

@Genevieve P. this was just Smartsheet having a spaz... and it decided to resolve itself. "No Quote" wasn't an option in the source column {JOB_QuoteNo}.

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.