Forum Discussion
Conditional Formatting with formula
Hi, I hope everyone is doing well.
=AND(LEFT($A1,2)="2G", $C1="KIMBERLEY")
Can someone please help me with this formula, it keeps giving me an error and I do not know whats wrong with it.
I want to fill a cell based on the start of a cell value (in column A), for example: 2G0003628, i need the "2G". And then on a different cell value (in column C) it needs to be a different town.
I am struggling to understand why my formula does not work.
depending on where you live / local setting you may need to use ; instead of , so it might be:
=AND(LEFT($A1;2)="2G"; $C1="KIMBERLEY")
as for 'hard coding' you can use a table on this or another sheet to help define the possible combinations and something like:
=AND(ISNUMBER(XMATCH(LEFT($A1;2); TABLE1[codes])); ISNUMBER(XMATCH($C1; TABLE1[cities])))
8 Replies
It is a new sheet with no data/values on, so there should not be any errors in cell A1 or C1.
This is the error I keep getting.
column A is the numbers
column C is the towns
I manually filled in the color for an example just for more clarifications. I recive new numbers (like 2G000556445) everyday and I want it to automatically color.
But like Mathetes said, i was trying to hard code it as I want to do it with multiple different numbers and towns and colors. So I'll look for a different way to do it.
And sorry for my bad english. It is not my 1st language.
In general it works
Then I do not know wrong. I tested other formulas and still nothing worked. Is there a setting or something that must be turned on or what?
depending on where you live / local setting you may need to use ; instead of , so it might be:
=AND(LEFT($A1;2)="2G"; $C1="KIMBERLEY")
as for 'hard coding' you can use a table on this or another sheet to help define the possible combinations and something like:
=AND(ISNUMBER(XMATCH(LEFT($A1;2); TABLE1[codes])); ISNUMBER(XMATCH($C1; TABLE1[cities])))
Help with Conditional Formatting Formula – Highlight 2G IDs with Specific Town
Hello,
I am trying to apply Conditional Formatting to highlight values in Column A (IDs) only when the following two conditions are both true:
- The ID in Column A starts with "2G"
- The corresponding Town in Column C is "KIMBERLEY"
Formula used:
=AND(LEFT($A2,2)="2G", $C2="KIMBERLEY")Setup:
- I selected the range: A2:A11
- Went to: Conditional Formatting → New Rule → Use a formula to determine which cells to format
- Pasted the formula above
- Applied my preferred formatting (e.g., green fill)
Expected Result:
Only the IDs in Column A that start with 2G and have "KIMBERLEY" in Column C should be highlighted.Question:
Can anyone confirm if this is the best formula for this scenario?
Are there any improvements or best practices I should consider?Thanks so much!
In addition to coming back with the clarifications that m_tarler has requested, I would suggest that you try to avoid what's called "hard coded values" in your formula. In the formula you've asked about the "2G" and "KIMBERLEY" are both instances of hard coded values. The formula will only work when those specific values are present, which means that any alternate circumstances or conditions will have to have their own distinct "hard-coded" values. See this link for a good summary of the pros and cons of hard-coded values, the times when they're helpful, but also the warnings about inappropriate use.
I don't see what error that formula would produce unless there is already an error in cell A1 or C1. Otherwise it should just return TRUE or FALSE
Maybe include a sample sheet
Also what you 'want' because you say you "need the "2G" and then something about column C "needs to be a different town" but those statements don't make much sense with the formula you included.
