指数公式

JennS_
JennS_ ✭✭
编辑01/12/23 在公式和函数

我正试图从另一张表(表称为SOW BB28-A跟踪)的特定标准拉入日期。例如，我有一个项目(舰队号)和保修开始日期。我想根据车队编号将保修日期拉到主表中。我已经尝试了INDEX公式一百万种不同的方法，它不适合我。

=指数（{SOW BB28-A跟踪距离2}，MATCH([机队号]@row， {SOW BB28-A跟踪距离3}，0)

我知道这应该很简单。我错过了什么?

· 在Facebook上分享 在Twitter上分享

答案

  • 克雷格伦贝格
    克雷格伦贝格 ✭

    我不完全理解你的问题，但是你必须索引一条信息，如果你能匹配你当前的表格上的一条信息与你参考的表格上的相同信息。当信息匹配时，索引信息将被拉过来:

    索引(参考表上的日期)，匹配(舰队)(电子邮件保护)(参考表上的船队编号)

    · 在Facebook上分享 在Twitter上分享
  • JennS_
    JennS_ ✭✭
    编辑01/11/23

    我在两张单独的纸上有一个船队号码。我想拉出特定车队编号的保修开始日期，并在另一张表中更新与之匹配的车队编号。见下面的母表。我正试图从下面的第二张图片中拉出保修日期，进入这张车队号码匹配的表格。

    image.png
    image.png


    · 在Facebook上分享 在Twitter上分享
  • 保罗H
    保罗H ✭✭✭✭✭✭

    可能只是末尾少了一个括号

    =指数（{SOW BB28-A跟踪距离2}，MATCH([机队号]@row， {SOW BB28-A跟踪距离3}，0)）

    · 在Facebook上分享 在Twitter上分享
  • JennS_
    JennS_ ✭✭

    嗨，那对我没用。我真的想不明白!

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理

    嗨@JennS_

    你的公式语法是正确的-我想尝试排除一些不同的事情。

    首先，检查以确保您的两个范围正在查看正确的列，并且选择了完整的列

    • {SOW BB28-A跟踪范围2}=保修开始日期
    • {SOW BB28-A range 3} =机队编号

    如果这是正确的，接下来要检查的是公式是否在当前工作表中为您的舰队编号找到匹配。

    =COUNTIF({SOW BB28-A range 3}，[舰队编号]@row)

    这应该会给你一个数字。如果你得到0，那么公式没有找到这两列之间的匹配-在这种情况下，我们需要知道你的两个[Fleet No]列是什么类型的列，以及如何创建/输入数字。

    最后要说的是……我看到你的公式是[舰队号]@row，但列名看起来是[舰队号]。带句号。请确保公式中的列名与当前工作表中的列名完全相同

    如果这没有帮助，让我们知道如果你看到一个公式错误的消息(和哪一个)，或者如果你得到一个不正确的结果!

    谢谢,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享
  • JennS_
    JennS_ ✭✭
    编辑01/12/23

    吉纳维芙-谢谢你的笔记。我把这些项目都按下面的公式进行了核对:

    =INDEX({SOW BB28-A保修开始日期}，MATCH([机队编号]@row)， {SOW BB28-A跟踪机队编号}，0))

    我得到的错误是# unseable

    我甚至试过:

    =INDEX({SOW BB28-A保修开始日期}，COUNTIF([机队编号]@row)， {SOW BB28-A跟踪机队编号}，0))

    还是不行。问题是，你是被限制在一张表格里做这种公式吗?例如，我有另一个列，它也对另一个表进行索引。这会引起问题吗?我也在主工作表中使用了链接单元格，但是我有很多链接单元格，我试图通过索引公式更容易地提取信息。任何额外的帮助都是感激的。

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理

    嗨@JennS_

    对于你的第一个公式，在MATCH(___)之后有一个额外的)应该被删除:

    =INDEX({SOW BB28-A保修开始日期})，MATCH([机号,{所以]@rowwbb28 - a跟踪船队编号}，0))


    对于COUNTIF公式，我打算完全单独测试它，只是为了看看它是否找到了匹配!这是一个故障排除步骤，而不是您的最终公式

    =COUNTIF({SOW BB28-A range 3}，[舰队编号]@row)


    但在此之前，看看删除多余的)是否能使第一个公式工作!即使在另一个列中使用INDEX(MATCH)，也应该能够在这个列中使用它。只要确保你没有复制/粘贴相同的公式和编辑引用因为这将更新整个工作表的引用。相反，应该从头开始创建新的引用，以确保它正在查看正确的列。

    如果有帮助，请告诉我!

    · 在Facebook上分享 在Twitter上分享
  • JennS_
    JennS_ ✭✭

    @Genevieve P。

    好吧!我更新了第一个公式，这次我得到了#INVALID COLUMN VALUE。进步! ?

    顺便说一句，国家核查确实有效。它返回一个数字1。

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理

    嗨@JennS_

    COUNTIF能起作用真是太好了!:)这意味着它应该找到一个匹配。

    #无效的列值通常意味着列类型不同于你放入其中的值。你能否证实这两个列是日期类型的列吗?

    {SOW BB28-A保修开始日期}和正在输入公式的列?

    · 在Facebook上分享 在Twitter上分享
  • JennS_
    JennS_ ✭✭

    @Genevieve P。

    我刚确认过，它们不都是日期列!所以我更新了它，它工作了!真不敢相信这么简单的事。谢谢你的帮助!!你是最棒的。

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理

    完全没问题!我很高兴我们最终做到了

    · 在Facebook上分享 在Twitter上分享

帮助文章参考资料欧宝体育app官方888

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
@CamSME<\/a> you'll use join and collect with children or decedents as the range. This video might help.<\/p>
\n \n https:\/\/youtu.be\/Dzo0UYjxMmI\n <\/a>\n<\/div>


<\/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":106279,"type":"question","name":"How do I create and If Formula pulling from another sheet to designate indicators?","excerpt":"Good Morning All, I am working on a Overall Health for a Project that looks like this: I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below In the first sheet for each Column I need to look at the…","categoryID":322,"dateInserted":"2023-06-09T16:40:18+00:00","dateUpdated":"2023-06-09T16:47:15+00:00","dateLastComment":"2023-06-11T19:20:33+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T21:34:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-11T19:35:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":55,"score":null,"hot":3372844251,"url":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","format":"Rich","lastPost":{"discussionID":106279,"commentID":380011,"name":"Re: How do I create and If Formula pulling from another sheet to designate indicators?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380011#Comment_380011","dateInserted":"2023-06-11T19:20:33+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-11T19:35:57+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/XSK1SHR157VN\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-11T16:45:30+00:00","dateAnswered":"2023-06-11T12:13:51+00:00","acceptedAnswers":[{"commentID":380000,"body":"

If it isn't weekly, then you would use a COUNTIFS in nested IFs.<\/p>

=IF(COUNTIFS({Criteria}, @cell = \"Infrastructure\", {Due Date}, @cell<= TODAY())> 0, \"Red\", IF(.....<\/p>


<\/p>

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.<\/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":106286,"type":"question","name":"Health Bubble Turn Colors when Status is Updated","excerpt":"Hello, I am new to Smartsheet. I am trying to get when I change my Status to \"Not Started\" I want the Health bubble to automatically turn grey. And when I change my Status to \"In Progress\" the Health bubble will turn yellow, etc. So far I have: IF(Status1 = \"Not Started\", \"Gray\", \" \"). I want it to change color for each…","categoryID":322,"dateInserted":"2023-06-09T19:29:22+00:00","dateUpdated":"2023-06-09T20:02:36+00:00","dateLastComment":"2023-06-09T21:14:52+00:00","insertUserID":162263,"insertUser":{"userID":162263,"name":"clairehunter","url":"https:\/\/community.smartsheet.com\/profile\/clairehunter","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-10T18:54:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162263,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":31,"score":null,"hot":3372684854,"url":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","format":"Rich","tagIDs":[254,440],"lastPost":{"discussionID":106286,"commentID":379933,"name":"Re: Health Bubble Turn Colors when Status is Updated","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379933#Comment_379933","dateInserted":"2023-06-09T21:14:52+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+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-10T18:54:41+00:00","dateAnswered":"2023-06-09T21:14:52+00:00","acceptedAnswers":[{"commentID":379933,"body":"

You will need a nested IF formula:<\/p>

=IF(Status@row=\"Not Started\", \"Gray\", IF(Status@row=\"In Progress\", \"Yellow\", IF(Status@row=\"Complete\", \"Green\")))<\/p>


<\/p>

Just add more IF Statements like above for what you need. Put all ) at the very end. Then you can turn it into a column formula by right clicking the cell the formula is in and selecting column formula.<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 遇到了父单元格聚合子任务名称的公式。

    我想捕获单个项目名称，由子任务跟踪，并将它们聚合在父单元格(所有项目名称)中。这样，当最小化时，我就不必展开来查看任务是针对哪些站点。同样对于Dashboard视图，查看哪些站点是被跟踪的父项目的一部分。截图……

  • 我如何创建和如果公式从另一个工作表拉来指定指标?

    大家早上好，我正在处理一个项目的整体运行状况，它看起来像这样:我需要通过使用if语句来更新灯光，该语句查看另一张工作表中三个不同的列，这些列在工作表中并不相邻，下面是该工作表的示例。在第一张工作表中，对于每个列，我需要查看…

  • 当状态更新时，生命泡泡会变颜色

    你好，我是Smartsheet的新手。我试图得到当我改变我的状态为“未开始”，我想要的健康气泡自动变成灰色。当我将自己的状态更改为“进行中”时，生命值气泡将变成黄色，等等。到目前为止，我有:IF(Status1 = "Not Started"， "Gray"， " ")。我想让它改变颜色…