I currently have a Google Sheet, with numerous sheets in it, to track contract deadlines for different clients. One of these sheets shows me all of the deadlines in Columns E:M for every contract. Each row is a separate contract.
我目前有一张Google表格,其中包含大量表格,用于跟踪不同客户的合约截止日期。其中一张表显示了每个合同中列E:M的所有截止日期。每一行都是一份单独的合同。
I'm trying to create a separate sheet that will show me the client's name, found in column A, if a date in columns E:M is Today. That way I can look at one page and see all the clients that have a deadline today. Here is what I tried with no success:
我正在尝试创建一个单独的工作表,它将显示客户的名称,如A列中所示,如果E:M列中的日期为今天。这样我就可以查看一个页面,查看今天截止日期的所有客户。这是我尝试过没有成功的:
=if('U/C(Dont Edit)'E2:M2=TODAY(),'U/C(Dont Edit)'A2,"")
"U/C(Dont Edit)" is the sheet that shows all of the contracts.
“U / C(不要编辑)”是显示所有合同的表格。
I believe the problem lies when I try to apply "=Today()" to a range(E:M). It seems to work fine if I just use E2=Today(). It looks like I could accomplish my goal using If statements within If statements, but that gets very messy very quickly and I'm hoping there is a better way.
我相信当我尝试将“= Today()”应用于范围(E:M)时,问题就在于此。如果我只使用E2 = Today(),它似乎工作正常。看起来我可以使用If语句中的If语句来完成我的目标,但是这很快就会非常混乱,我希望有更好的方法。
Thanks in advance for your help.
在此先感谢您的帮助。
2 个解决方案
#1
You can do it with the COUNTIF() function per row:
您可以使用每行的COUNTIF()函数执行此操作:
=IF(COUNTIF(E2:M2,TODAY())>0,A2,"")
But I think a better solution is to use the FILTER() function. It will give you a list of names:
但我认为更好的解决方案是使用FILTER()函数。它会给你一个名单:
=FILTER(A2:A,(E2:E=TODAY())+(F2:F=TODAY())+(G2:G=TODAY())+(H2:H=TODAY())+
(I2:I=TODAY())+(J2:J=TODAY())+(K2:K=TODAY())+(L2:L=TODAY())+(M2:M=TODAY()))
#2
I suggest @dePatinkin's solution (for and additional column, say N, containing the COUNTIF formula from Row2 downwards) in combination with a query (in another sheet):
我建议使用@dePatinkin的解决方案(用于和附加列,比如N,包含Row2向下的COUNTIF公式)和查询(在另一个表中):
=query('U/C(Dont Edit)'!A:N,"select * where N is not NULL")
This assumes your existing sheet has the columns labelled.
这假定您现有的工作表标有列。
You might combine the above with Conditional formatting to indicate which column has the date in it (today) that triggered the row selection.
您可以将上述内容与条件格式相结合,以指示哪个列具有触发行选择的日期(今天)。
#1
You can do it with the COUNTIF() function per row:
您可以使用每行的COUNTIF()函数执行此操作:
=IF(COUNTIF(E2:M2,TODAY())>0,A2,"")
But I think a better solution is to use the FILTER() function. It will give you a list of names:
但我认为更好的解决方案是使用FILTER()函数。它会给你一个名单:
=FILTER(A2:A,(E2:E=TODAY())+(F2:F=TODAY())+(G2:G=TODAY())+(H2:H=TODAY())+
(I2:I=TODAY())+(J2:J=TODAY())+(K2:K=TODAY())+(L2:L=TODAY())+(M2:M=TODAY()))
#2
I suggest @dePatinkin's solution (for and additional column, say N, containing the COUNTIF formula from Row2 downwards) in combination with a query (in another sheet):
我建议使用@dePatinkin的解决方案(用于和附加列,比如N,包含Row2向下的COUNTIF公式)和查询(在另一个表中):
=query('U/C(Dont Edit)'!A:N,"select * where N is not NULL")
This assumes your existing sheet has the columns labelled.
这假定您现有的工作表标有列。
You might combine the above with Conditional formatting to indicate which column has the date in it (today) that triggered the row selection.
您可以将上述内容与条件格式相结合,以指示哪个列具有触发行选择的日期(今天)。