返回外部工作表数组中的最大日期值

戈登
戈登 ✭✭✭✭✭
在公式和函数

你好,

我试图返回最大日期从外部引用表的系统创建的日期列(“响应日期”)基于匹配外部表中的电子邮件地址/名称从当前表的另一列中的多个值。

我试过使用=MAX(COLLECT()回复日期、电邮地址栏、当前工作表中的电子邮件地址))，但无论电子邮件地址标准如何，它似乎都会返回最大日期。

我需要先做一个匹配基于电子邮件地址，然后收集吗?或者使用INDEX、MATCH和COLLECT是正确的逻辑吗?

任何帮助或见解都非常感谢!

· 在Facebook上分享 在Twitter上分享

最佳答案

答案

  • 大卫Joyeuse
    大卫Joyeuse ✭✭✭✭✭

    嗨@Gordon

    =MAX(COLLECT({Response Date}， {Email Address}，[Email Address]@row)是您正在寻找的。

    调整范围和电子邮件地址列名称，以有效地在您的工作表中使用。


    希望有帮助!

    · 在Facebook上分享 在Twitter上分享
  • 戈登
    戈登 ✭✭✭✭✭

    嗨@David Joyeuse

    谢谢你的建议。不幸的是，当我使用您的公式(将{Response Date}和{Email Address}替换为另一个工作表中列范围的外部引用)时，公式返回{Response Date}列中的最新日期，忽略电子邮件地址标准。

    COLLECT对整个列的外部表引用是否有效?列必须在外部工作表中彼此相邻吗?

    谢谢你的帮助!

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    COLLECT应该适用于此，并且列不必彼此相邻。


    确保两张表格上的电子邮件数据是匹配的。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 戈登
    戈登 ✭✭✭✭✭

    列类型必须在“条件范围”和“条件”之间匹配吗?

    {Email Address}是一个来自Smartsheet表单的system Created By列，当前工作表中的{Email Address}@row引用是一个联系人列表列。的{响应日期}列在外部工作表中设置为系统创建日期列。

    谢谢。

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    您需要确保Created By列中的数据与[Email Address]列中的数据匹配。如果你在一张表格中有电子邮件，而在另一张表格中有联系方式，它们将不匹配。


    如果你有(电子邮件保护)在Created By列和联系人类型列中的John Doe中，由于文本字符串不同，它们将无法相互匹配。

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享
  • 戈登
    戈登 ✭✭✭✭✭

    这两个列在Smartsheet中都显示为电子邮件地址，但也许列类型导致不匹配和标准不适用?

    · 在Facebook上分享 在Twitter上分享
  • 戈登
    戈登 ✭✭✭✭✭
    编辑08/25/20

    好了，现在我想我知道为什么我的公式不起作用了。系统列创建日期与Smartsheet评估的值不匹配:

    注释2020-08-24 152421.png

    的创建上面的列来自另一个捕获表单数据的工作表，该工作表具有自动将行移动到此工作表中的功能。原始源表创建已设置为系统列创建(日期)列。

    的回应上面的column被设置为日期列类型，并且是一个简单的reference =[Created}@row公式。

    如上所示，无论出于何种原因，Smartsheet在公式的结果中错误地显示了日期。

    8/21/20的结果是从上面的=MAX(COLLECT公式输出的，这就是为什么我认为我的公式是错误的。

    我得到相同的“8/21/20”的结果，即使我不使用的帮助回应列和直接引用创建=MAX(COLLECT)函数中。我甚至尝试使用=DATEVALUE([Created]@row)函数回应列-同样的结果。

    知道这是怎么回事吗?我完全搞不懂为什么8/20/20下午4:28和8/21/20是一样的。在其他行中也可以看到相同的错误，而有些日期是正确的。

    · 在Facebook上分享 在Twitter上分享
  • 戈登
    戈登 ✭✭✭✭✭

    @Paul新来的谢谢!我没有意识到Smartsheet在时区和日期方面如此“智能”。您可能会认为在用户级别上应该有一致性，这样公式和函数都应该“聪明”到足以对此进行调整。我想我有点理解他们如何以及为什么要基于UTC构建日期/时间并在用户级别进行转换。尽管如此，它还是会导致很多困惑。

    我通过从Created列中去掉日期文本并将其转换回日期值来解决这个问题。

    · 在Facebook上分享 在Twitter上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    很高兴你解决了问题，也很乐意帮忙。️

    thinkspi.com

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!

进入赢取免费会议通行证到ENGAGE 2023

 了解更多
@Pamela Wagner<\/a> There's nothing in core Smartsheet. <\/p>

If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>

Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/a><\/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":106845,"type":"question","name":"Count function","excerpt":"Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> \"N\/A\") The Test Script # column is the…","categoryID":322,"dateInserted":"2023-06-23T17:50:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":127983,"lastUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":46,"score":null,"hot":3375096049,"url":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106845,"commentID":382093,"name":"Re: Count function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382093#Comment_382093","dateInserted":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+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-23T19:30:46+00:00","dateAnswered":"2023-06-23T19:28:59+00:00","acceptedAnswers":[{"commentID":382092,"body":"

@Carol-Anne Cerbone<\/a> <\/p>

How about this?<\/p>

=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], =\"N\/A\")<\/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"}]},{"discussionID":106844,"type":"question","name":"Avg number of selections per condition","excerpt":"Hi there, I need some help. I'm trying to calculate the avg number of issues per condition in our master tracker. The issues are in a multi-value cell {Rev Rec Master Tracker Range 1} while the condition is a single-value cell in {Rev Rec Master Tracker Range 2} with values either as \"Backlog\" or \"Net New\". I'd like to…","categoryID":322,"dateInserted":"2023-06-23T17:36:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T18:36:10+00:00","insertUserID":162710,"insertUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162710,"lastUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":37,"score":null,"hot":3375089541,"url":"https:\/\/community.smartsheet.com\/discussion\/106844\/avg-number-of-selections-per-condition","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106844\/avg-number-of-selections-per-condition","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106844,"commentID":382062,"name":"Re: Avg number of selections per condition","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382062#Comment_382062","dateInserted":"2023-06-23T18:36:10+00:00","insertUserID":162710,"insertUser":{"userID":162710,"name":"Leah Schmid","url":"https:\/\/community.smartsheet.com\/profile\/Leah%20Schmid","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtenj_SBCEO-5DL1Uq6fCj1Eudu-GWv5LWQWq-DX-A=s96-c","dateLastActive":"2023-06-23T19:56:32+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-23T18:35:49+00:00","dateAnswered":"2023-06-23T18:26:57+00:00","acceptedAnswers":[{"commentID":382060,"body":"

So for \"Backlog\" you would want it to count 3 Action Plan Notes and then divide by 2 \"Backlog\" entries? If so, try this:<\/p>

=COUNTM(COLLECT({Action Plan Notes}, {RR Type}, @cell = \"Backlog\")) \/ COUNTIFS({RR Type}, @cell = \"Backlog\")<\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 只复制某些列到一个新的工作表?

    我正试图将两张纸结合在一起，一张是用于购票的新纸，另一张纸已被另一个团队使用，该团队有一个人的门票信息，但也有他们团队的额外信息。我不希望团队在填写表格的同时还必须在输入表格中输入数据。我…

  • 计数功能

    你好，我需要你帮我弄个公式。我有一列，我想计数，但只基于一个值在不同的列。我试过下面的公式，但它不起作用，我不知道我做错了什么。=countif([Test Script #]:[Test Script #]， [Pass ?]:[Pass ?]， <> "N/A") Test Script #列是…

  • 每个条件的平均选择数

    你好，我需要一些帮助。我正在计算主跟踪器中每个条件的平均问题数。问题在多值单元{Rev Rec Master Tracker Range 1}中，而条件是{Rev Rec Master Tracker Range 2}中的单值单元，值为“Backlog”或“Net New”。我愿意......