英文:
Can Google Sheets use Conditional Formatting to validate an entry through vlookup?
问题
我被困扰了，可能想得太多了。我有一个包含“Check”标签和“Invoice”标签的表格。任何到达的支票，无论是用于发票还是其他用途，都在“Check”标签上进行跟踪，而任何已发送的带有应付金额的发票都在“Invoice”标签上。
在“Check”标签上，我们有一个条件格式，用于验证是否具有支票的发票是否是有效的发票编号；列C。在下一列中，我们输入收到的金额。我想要条件格式验证输入的金额是否与预期的付款金额匹配。有效的话，不显示颜色。无效的话，显示红色。
有没有办法实现这个？可以使用条件格式通过vlookup来验证输入吗？
谢谢！
示例表格
我尝试将这个公式输入到“Check”标签上的条件格式中，但没有任何反应。
=D2=vlookup(D2,indirect("Invoices!$c$4:d"),2,0)
我还尝试过使用Index/Match与条件格式进行研究，但我没有找到与这个需求足够相似的内容。
英文:
I'm stumped and could be overthinking this. I have a sheet that has a Check tab and Invoice tab. Any checks that arrive, whether they be for an Invoice or not, are tracked on the Check tab while any Invoices sent out with expected amount due are on the Invoice tab.
On the Check tab, we have a conditional format that validates if an Invoice received with a check is a valid Invoice number; Col C. In the next column, we enter the amount received. I'd like to have Conditional Formatting validate if the amount entered matches what was expected for payment. Valid, no color. Invalid, red.
Is there a way to do this? Using Conditional Format to validate an entry via vlookup?
Thank you!!
Example Sheet
I tried entering this formula into Conditional Formatting on the Check Tab but nothing happens.
=D2=vlookup(D2,indirect("Invoices!$c$4:d"),2,0)
I also tried researching Index/Match with Conditional Formatting but I'm not seeing anything that's similar enough to this need.
答案1
得分: 1
$D4<>vlookup($C4,indirect("Invoices!$C$4:$D"),2,)
英文:
<!-- language-all: js -->
Slightly modifying your formula with:
=$D4<>vlookup($C4,indirect("Invoices!$C$4:$D"),2,)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论