返回外部工作表数组中的最大日期值
你好,
我试图返回最大日期从外部引用表的系统创建的日期列(“响应日期”)基于匹配外部表中的电子邮件地址/名称从当前表的另一列中的多个值。
我试过使用=MAX(COLLECT()回复日期、电邮地址栏、当前工作表中的电子邮件地址)),但无论电子邮件地址标准如何,它似乎都会返回最大日期。
我需要先做一个匹配基于电子邮件地址,然后收集吗?或者使用INDEX、MATCH和COLLECT是正确的逻辑吗?
任何帮助或见解都非常感谢!
最佳答案
-
保罗新来的 ✭✭✭✭✭✭
答案
-
大卫Joyeuse ✭✭✭✭✭
=MAX(COLLECT({Response Date}, {Email Address},[Email Address]@row)是您正在寻找的。
调整范围和电子邮件地址列名称,以有效地在您的工作表中使用。
希望有帮助!
-
戈登 ✭✭✭✭✭
谢谢你的建议。不幸的是,当我使用您的公式(将{Response Date}和{Email Address}替换为另一个工作表中列范围的外部引用)时,公式返回{Response Date}列中的最新日期,忽略电子邮件地址标准。
COLLECT对整个列的外部表引用是否有效?列必须在外部工作表中彼此相邻吗?
谢谢你的帮助!
-
保罗新来的 ✭✭✭✭✭✭
-
戈登 ✭✭✭✭✭
列类型必须在“条件范围”和“条件”之间匹配吗?
{Email Address}是一个来自Smartsheet表单的system Created By列,当前工作表中的{Email Address}@row引用是一个联系人列表列。的{响应日期}列在外部工作表中设置为系统创建日期列。
谢谢。
-
戈登 ✭✭✭✭✭
这两个列在Smartsheet中都显示为电子邮件地址,但也许列类型导致不匹配和标准不适用?
-
戈登 ✭✭✭✭✭
好了,现在我想我知道为什么我的公式不起作用了。系统列创建日期与Smartsheet评估的值不匹配:
的创建上面的列来自另一个捕获表单数据的工作表,该工作表具有自动将行移动到此工作表中的功能。原始源表创建已设置为系统列创建(日期)列。
的回应上面的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是一样的。在其他行中也可以看到相同的错误,而有些日期是正确的。
-
保罗新来的 ✭✭✭✭✭✭
-
保罗新来的 ✭✭✭✭✭✭
帮助文章参考资料欧宝体育app官方888
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":"