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?
Answers
-
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)
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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}.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!