This particular question is regarding highlighting dates on a pre-made calendar.
这个特殊问题是关于在预制日历上突出显示日期。
Please see screenshot below:
请看下面的截图:
I need to highlight the cell in the calendar that matches both the task and the date in the data table. For example- see L3 and P4. In the screenshot they are highlighted manually for demonstration.
我需要在日历中突出显示与数据表中的任务和日期相匹配的单元格。例如 - 见L3和P4。在屏幕截图中,它们会手动突出显示。
Can someone please help me out with this. I've been using AND, but screwing up somewhere with the $.
有人可以帮我解决这个问题。我一直在使用AND,但是用$来搞砸了。
2 个解决方案
#1
0
If you aren't entering any data and using this as a visual tool you can spoof the spreadsheet by placing a period into the cell that matches the values you need, and then conditional format based on specific text and enter the period as the text.
如果您没有输入任何数据并将其用作可视化工具,则可以通过在单元格中放置与您需要的值匹配的句点来欺骗电子表格,然后根据特定文本设置条件格式并输入句点作为文本。
This image demonstrates it as two separate tables; One with only the formula used to look for the values, and the second one formatting the background and font to be the same color (effectively hiding the period)
此图像将其显示为两个单独的表格;一个只有用于查找值的公式,第二个将背景和字体格式化为相同的颜色(有效地隐藏周期)
It its definitely a workaround and will only work if this is a visual tool.
它绝对是一种解决方法,只有在这是一个可视化工具时才会起作用。
#2
0
Assuming your Data Table goes down to row 7, try entering this Conditional Formatting formula with cell B3 selected:
假设您的数据表下至第7行,请尝试输入此条件格式公式,并选择单元格B3:
=COUNTIFS($AI$4:$AI$7,$B3,$AJ$4:$AJ$7,C$2)>0
#1
0
If you aren't entering any data and using this as a visual tool you can spoof the spreadsheet by placing a period into the cell that matches the values you need, and then conditional format based on specific text and enter the period as the text.
如果您没有输入任何数据并将其用作可视化工具,则可以通过在单元格中放置与您需要的值匹配的句点来欺骗电子表格,然后根据特定文本设置条件格式并输入句点作为文本。
This image demonstrates it as two separate tables; One with only the formula used to look for the values, and the second one formatting the background and font to be the same color (effectively hiding the period)
此图像将其显示为两个单独的表格;一个只有用于查找值的公式,第二个将背景和字体格式化为相同的颜色(有效地隐藏周期)
It its definitely a workaround and will only work if this is a visual tool.
它绝对是一种解决方法,只有在这是一个可视化工具时才会起作用。
#2
0
Assuming your Data Table goes down to row 7, try entering this Conditional Formatting formula with cell B3 selected:
假设您的数据表下至第7行,请尝试输入此条件格式公式,并选择单元格B3:
=COUNTIFS($AI$4:$AI$7,$B3,$AJ$4:$AJ$7,C$2)>0