•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Conditional formatting based on a range of text in 2nd sheet
Our take
In your two-sheet workbook, you want to apply conditional formatting to highlight order numbers based on their status, which is listed on the second sheet. Your current formula, `=$B1='Sheet2!A1`, works for a single status but does not accommodate a range. To achieve your goal, you can use the formula `=COUNTIF(Sheet2!$A$1:$A$10, $B1) > 0`. This approach checks if the status in column B matches any entry in the specified range on the second sheet, allowing for effective conditional formatting based on
Hey!
I have a two sheet workbook. First sheet has column A: list of order numbers that should be highlighted based on status and column B: status. the status text comes from a list in the second sheet. all possible statuses are listed from A1:A10 on the second sheet, but there might select colors per status (like from A1:A3 and A10 would all need the same color)
Right now I have as a formula:
=$B1='Sheet2!A1
and that works, but trying to use a range doesn't work
=$B1='Sheet2!$A1:$A$5
I'm certain I'm overlooking a basic issue, but my googling skills are failing me 😞
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#rows.com#cloud-based spreadsheet applications#AI formula generation techniques#Excel compatibility#Excel alternatives for data analysis#formula generator#Excel alternatives#conditional formatting#workbook#sheet#order numbers#status#formula#range#highlighted#status text#colors per status#list#A1:A10#A1:A5