奇怪的交叉表参考问题
好的，我有三张纸，
- 作业单
- 成本表
- 报价数据表(通过Zap从Xero导入)。
在工作和成本表中，我感兴趣的是记录报价号码。在作业和成本表的报价号栏中，我分别有这些公式....
= IFERROR(指数({DATA_QuoteNo}匹配(DATA_ID @row, {DATA_ID}, 0)),“没有引用”)
= IFERROR(指数({JOB_QuoteNo}匹配(JOB_ID @row, {JOB_ID}, 0)),“没有引用”)
这是奇怪的部分....第一个公式正确地返回引号数，但第二个公式返回“No quote”…我以为我犯了某种公式错误，我把公式简化为……
=指数({JOB_QuoteNo},配(JOB_ID @row, {JOB_ID}, 0))
但是结果是“无报价”，这意味着成本表将作业表单元格读取为“无报价”，尽管所有列都填充了实际报价号码……奇怪。
有人有什么想法吗?
答案
-
斯科特•彼得斯 ✭✭✭✭✭嗨@BullandKhmer-如果没有看到数据样本，很难确切地说问题可能是什么，但我总是建议的一个故障排除步骤是右键单击表格中构建公式的任何单元格，选择“管理引用”，然后检查每个单元格，以确保引用应用于正确的列(以及整个列，而不仅仅是某些特定的单元格)。
-
吉纳维芙P。 员工管理我同意斯科特的观点!
从你的描述，我要做的第一件事是检查你的第一个参考是指向:
=指数({JOB_QuoteNo}匹配([JOB_ID] @row {JOB_ID}, 0))
如果没有匹配，我预计会出现no MATCH错误，而不是“no Quote”值。是否有任何与“无报价”相关的职位标识?
我同意看到源数据会有帮助，但请屏蔽敏感数据。
欢呼,
吉纳维芙
-
BullandKhmer ✭✭✭✭✭
@Genevieve P。这只是一个有空格的Smartsheet…它决定自行解决。在源列{JOB_QuoteNo}中没有“No Quote”选项。
帮助文章参考资料欧宝体育app官方888
类别
=INDEX({Data SHPType}, MATCH(Course@row, {Data Course}, 0<\/strong>))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106455,"type":"question","name":"Formula to count items in a cell separated by a semicolon","excerpt":"Good afternoon, could someone help me with a formula to count items in a cell separated by a semicolon and put the total in another? I have a column called: \"MRA Actives codes\", in which there are different medical codes separated by a semicolon, for example: I70.0: Atherosclerosis of aorta; I50.9: Heart failure…","categoryID":322,"dateInserted":"2023-06-14T19:20:24+00:00","dateUpdated":null,"dateLastComment":"2023-06-15T11:46:15+00:00","insertUserID":162381,"insertUser":{"userID":162381,"name":"VirginiaLazor","title":"Medical Risk Adjustment Auditor","url":"https:\/\/community.smartsheet.com\/profile\/VirginiaLazor","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!b1Dom3_UQfo!xekpcXON8bo!9w33zAjZR8S","dateLastActive":"2023-06-15T12:10:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-15T12:44:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":41,"score":null,"hot":3373603599,"url":"https:\/\/community.smartsheet.com\/discussion\/106455\/formula-to-count-items-in-a-cell-separated-by-a-semicolon","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106455\/formula-to-count-items-in-a-cell-separated-by-a-semicolon","format":"Rich","lastPost":{"discussionID":106455,"commentID":380725,"name":"Re: Formula to count items in a cell separated by a semicolon","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380725#Comment_380725","dateInserted":"2023-06-15T11:46:15+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-15T12:44:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-15T12:03:29+00:00","dateAnswered":"2023-06-15T11:44:52+00:00","acceptedAnswers":[{"commentID":380724,"body":"
