索引公式与If And
你好,
第一次发帖。我有几张链接在一起的表。长话短说是网络应用程序粘贴到一个表格填充到另一个审查就业资格。然后,该工作表将为我的下拉列表提供数据到活动工作表中,活动工作表将根据该人的姓名拉出相应的数据行。这一切都在起作用。我现在正试图从一个“帮助表”中创建一个公式,其中我有秋季和春季的津贴,我想根据F或S是否被选中以及课程是15还是20个小时来填充学期。
我一直在使用索引,但我想我可以使用If和公式,但不确定如何使它工作。这是我一直在使用的索引公式,它是有效的,但是如果我有一个人只在秋天做助教,我希望春天的津贴列保持空白,春天的助教和秋天的津贴列保持空白。
=INDEX({UpdateRates 15Stipend}, MATCH()(电子邮件保护), {UpdateRates Term}, 0))
知道我哪里做错了吗?
非常感谢!
罗莉
最好的答案
答案
-
保罗新来的 ✭✭✭✭✭✭
-
maineL ✭
抱歉,我点击了,是,回答。是的,最左边的Term列是F/S列。不,它只能是其中之一。
-
保罗新来的 ✭✭✭✭✭✭
-
maineL ✭
这只适用于春季的15小时。我想太多了。看起来15个津贴栏和20个津贴栏都有相同的金额。如果我把Term改成F,这些列就是空的。这就是我想要的,但是如果S被选中,我希望F津贴列是空的。
nk。明白了吗?
-
保罗新来的 ✭✭✭✭✭✭
-
maineL ✭
在之前的主题中,公式是成立的,但我想让它做下面的事情。
Fstipend/Sstipend各自列中的F/S项
15/20小时更改为相应津贴栏和美元金额的正确美元金额。现在它在春季津贴栏上做了20个小时,但在春季栏上不是15个小时。我想我做错了什么?
在完美的情况下,我希望Term和hours能够用正确的金额填充正确的津贴列。我希望为列做一个公式,但我猜它将不得不是一个“单元格”公式代替?
再次感谢!不好意思,绕路了!
罗莉
-
保罗新来的 ✭✭✭✭✭✭
这应该是一个列式,但你需要两个单独的公式。一个是春季列,开头是“S”,另一个是秋季列,开头是“F”。
到底是什么公式产生了这个误差是什么误差?
thinkspi.com
-
maineL ✭
哦,是的,我试着把所有东西都向左对齐。那会有什么不同吗?我可以让它恢复正常。因此,根据上面的公式,我将无法自动填充列,因为有两个单独的公式。如果我创建额外的列(一个用于15小时的F和S,另一个用于20小时的F和S),这个工作吗?
-
保罗新来的 ✭✭✭✭✭✭
如果你调整了格式,那就没有问题了。如果你没有做任何格式化,那么我们可能不得不稍微调整一下公式。
您可以在Spring列中应用上述列公式。“两个不同的公式”一个是春季的,一个是秋季的。两者都应该能够作为列公式应用。一个在春天一列,另一个在秋天一列(在把s换成f之后)。
thinkspi.com
-
maineL ✭
我看这和那20个小时有关。如果有帮助的话?
-
保罗新来的 ✭✭✭✭✭✭
帮助文章参考资料欧宝体育app官方888
类别
Please use the formula I provided. It was intentional for the character \"~\" to be included. <\/p>
=MID(Name@row, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)) + 1, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 6)) - 1 - FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)))<\/p>
Kelly<\/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":106552,"type":"question","name":"Problem w\/Index - Match formula","excerpt":"I using the following formula, =INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}), 0) And it works for the first row when i input in the contract number into the column. On subsequent rows when i input the contract number it pulls back incorrect information and if I put in a fictious number it pulls…","categoryID":322,"dateInserted":"2023-06-16T15:13:32+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:36:24+00:00","insertUserID":162467,"insertUser":{"userID":162467,"name":"Ed Smartsheet","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Ed%20Smartsheet","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJyYOjKt3k3tneIFbUPuoJzZbnd9quLS-Wdt5b7l=s96-c","dateLastActive":"2023-06-16T19:45:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3373859396,"url":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106552,"commentID":381031,"name":"Re: Problem w\/Index - Match formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381031#Comment_381031","dateInserted":"2023-06-16T15:36:24+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-16T19:45:15+00:00","dateAnswered":"2023-06-16T15:36:24+00:00","acceptedAnswers":[{"commentID":381031,"body":"
=INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}, 0))<\/strong><\/p>