英文:
conditional format based on text in cells
问题
我正在处理一个包含2个工作表的电子表格，一个工作表名为“Search”，另一个名为“raw_data”。
“raw_data”工作表将有3列A、B和C，其中包含管理员输入的邮政编码数据。
当用户在“Search”工作表的单元格A1中输入邮政编码时，该单元格将根据此邮政编码是否在“raw_data”工作表的列A、B或C中找到而进行条件格式设置。
如果在列A中找到，将突出显示为红色，列B为琥珀色，列C为绿色。
我知道这一定是可能的，但我周末思绪混乱，无法前进。感谢任何帮助。
英文:
I'm working on a spreadsheet which has 2 sheets, 1 sheet called 'Search' and one called 'raw_data'.
The 'raw_data' sheet will have 3 columns A,B and C which holds the postcode data the administrator will enter in.
When a user enters in a postcode within cell A1 on the 'Search' sheet, this cell will then conditional format based on whether this postcode is found in column A,B or C in the 'raw_data' sheet.
If found in column A it will highlight Red, Column B Amber and Column C Green
I know this must be possible but have my weekend head on and not getting anywhere. Any help appreciated.
Thanks
答案1
得分: 0
在“搜索”表格的单元格“A1”中，添加以下三个“条件格式规则”，在“格式单元格如果...”下，选择“自定义公式为”，并输入以下公式：
=COUNTIF(INDIRECT("raw_data!A:A"),A$1)>=1
=COUNTIF(INDIRECT("raw_data!B:B"),A$1)>=1
=COUNTIF(INDIRECT("raw_data!C:C"),A$1)>=1
选择您要应用于每个规则的颜色。这是每个规则应该如何看起来的示例：
英文:
In the Search sheet, cell A1, add the following three "Conditional format rules", under "Format cells if...", select "Custom formula is" and enter the following formula:
=COUNTIF(INDIRECT("raw_data!A:A"),A$1)>=1
=COUNTIF(INDIRECT("raw_data!B:B"),A$1)>=1
=COUNTIF(INDIRECT("raw_data!C:C"),A$1)>=1
Select the color you want to apply to each rule. This is a sample of how each rule should look:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论