Question description:
This user has given permission to use the problem statement for this
blog.
Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?
Solved by Z. U. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
21/08/2017 - 01:14
yeah so the rule applies for each column exclusively so for the rule to apply to D9>D32 it needs to reference D4
Excelchat Expert
21/08/2017 - 01:14
Hello, I understand that you need help win building a conditional formatting that checks if the first four characters are 1358, right?
User
21/08/2017 - 01:14
yes exactly
Excelchat Expert
21/08/2017 - 01:14
Will you be able to give a sample document that we can work on?
User
21/08/2017 - 01:14
but each column is exclsuive so, E will need to reference E4
User
21/08/2017 - 01:15
i cant sorry i cant share my companies materila
User
21/08/2017 - 01:15
Ill send you my current formula though
Excelchat Expert
21/08/2017 - 01:15
I'm not really asking for the actual data. Just a sample data that we can work on.
Excelchat Expert
21/08/2017 - 01:15
That way, we can test if the CF i'll be creating fits your requirement.
User
21/08/2017 - 01:15
I can't upload anything though
User
21/08/2017 - 01:15
=IF(LEFT(D$4,3)="'13","D9>D32","")
Excelchat Expert
21/08/2017 - 01:16
You don't have to upload anything. Just Try to type a few sample data.
User
21/08/2017 - 01:16
this is what Ive tried to use but it doesnt work
User
21/08/2017 - 01:16
sorry this one
User
21/08/2017 - 01:16
=IF(LEFT(D$4,4)="'135","D9>D32","")
Excelchat Expert
21/08/2017 - 01:16
Yes because conditional formatting will only accept TRUE or FALSE.
User
21/08/2017 - 01:16
can you explain?
Excelchat Expert
21/08/2017 - 01:17
Conditional formatting will check if your statement is TRUE or FALSE. If it is true then it will highlight the cell.
Excelchat Expert
21/08/2017 - 01:17
Otherwise, it won't do anything.
User
21/08/2017 - 01:17
okay so how do i fix it?
Excelchat Expert
21/08/2017 - 01:17
For example, your formula might be:
Excelchat Expert
21/08/2017 - 01:17
=left(d4,4)="1358"
Excelchat Expert
21/08/2017 - 01:18
But I can't tell you exactly what the formula should be because I have no clue how your data looks like.
User
21/08/2017 - 01:18
so from D9:D20 i have values
User
21/08/2017 - 01:18
then from D32:D43 i have calues
User
21/08/2017 - 01:18
values*
Excelchat Expert
21/08/2017 - 01:18
Do you see the document preview to the right?
User
21/08/2017 - 01:19
and i need it to highlight red if D9>D32
User
21/08/2017 - 01:19
and so on for each cell but only if first four characters of D4=1358
User
21/08/2017 - 01:19
no its blocked
User
21/08/2017 - 01:19
does that make sense to you though?
Excelchat Expert
21/08/2017 - 01:19
So it compares D9 with D32, D10 with D33 and so on?
User
21/08/2017 - 01:20
yeah exactly
Excelchat Expert
21/08/2017 - 01:20
And what's the role of D4?
Excelchat Expert
21/08/2017 - 01:20
Does D4 change?
User
21/08/2017 - 01:21
yes so only if it has 1358 as the first four characters
Excelchat Expert
21/08/2017 - 01:21
I mean is it always checking D4 or does it move downwards like D9 to d10 and d32 to d33?
User
21/08/2017 - 01:21
thats the only time i want the conditional formatting to apply to D9>D32, and so on
User
21/08/2017 - 01:21
no it doesnt
User
21/08/2017 - 01:21
d4 is always the same
Excelchat Expert
21/08/2017 - 01:22
=and(left($D$4,4)="1358",D9>D32)
User
21/08/2017 - 01:23
that make sense?
Excelchat Expert
21/08/2017 - 01:23
Entering Explanation Phase
Excelchat Expert
21/08/2017 - 01:23
We'll use the AND() function to generate a TRUE or FALSE value for the custom conditional formatting formula.
Excelchat Expert
21/08/2017 - 01:24
I just tried that formula in my conditional formatting and it works based on how I understood your problem.
User
21/08/2017 - 01:24
ok it works for D column
User
21/08/2017 - 01:24
now i need it for columns D:S
User
21/08/2017 - 01:25
but so like column E references E4
User
21/08/2017 - 01:25
F references F4 and so on
User
21/08/2017 - 01:25
that make sense?
Excelchat Expert
21/08/2017 - 01:25
If it is in E, it uses E4 and then compares E9 with E32?
User
21/08/2017 - 01:26
exactly
Excelchat Expert
21/08/2017 - 01:26
=and(left(D$4,4)="1358",D9>D32)
User
21/08/2017 - 01:26
and then use the format painter?
Excelchat Expert
21/08/2017 - 01:26
Use that and make sure you encompass D9:S20 for the range of the conditional formatting or use the format painter.
Excelchat Expert
21/08/2017 - 01:27
Entering Discussion Phase
User
21/08/2017 - 01:27
hmm doesnt seem to be working
User
21/08/2017 - 01:27
works for column D
User
21/08/2017 - 01:27
but not the others
Excelchat Expert
21/08/2017 - 01:27
I just used the same formula and it works in the document preview.
Excelchat Expert
21/08/2017 - 01:28
Unfortunately, you don't have access to it and I don't see what you are doing.
User
21/08/2017 - 01:30
=AND(LEFT(D$4,4)="1358",D9>D32)
User
21/08/2017 - 01:30
just tried that
User
21/08/2017 - 01:30
for some reason its not working
Excelchat Expert
21/08/2017 - 01:30
I can't really tell you what you are doing wrong because I don't see what you've done so far. :(
Excelchat Expert
21/08/2017 - 01:30
I guarantee you that formula works though.
User
21/08/2017 - 01:31
ok ill play with it a bit
User
21/08/2017 - 01:31
thanks for your help
Excelchat Expert
21/08/2017 - 01:32
Can you access Drive.google.com?
Excelchat Expert
21/08/2017 - 01:32
https://drive.google.com/file/d/0B1j34SU3SZazTjJsalFFZUxSQnc/view?usp=sharing
Excelchat Expert
21/08/2017 - 01:33
If you could find a way to download that file, I've applied the formula there and you will see that it works.
Excelchat Expert
21/08/2017 - 01:33
Please save that file.
Excelchat Expert
21/08/2017 - 01:33
We have 15 seconds to go. Please make sure you copy the path.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
