SOLVED

New Contributor

# Excel - Nested formulas in conditional formatting - Nightmare!

I can not figure out how to conditionally format other rows that match the value highlighted in column D. Column D is formatted by Column AB containing "Not Loaded" - I need to highlight the other rows of data that contain any matching values of highlighted cells in Column D. Basically, I am running commission for about 100 salespeople. A new policy requires salespeople to upload photos of product taken in on trade to a site. If not loaded, no commission on original deal until completed. To take out the entire deal would be to remove the same invoice number matching with Column AB containing "Not Loaded" -

Please refer to screenshot for reference. I need to highlight the invoice number the green arrow is pointing to, and any other instances this occurs with different invoice numbers that are highlighted/Not Loaded. This data will be well over 20,000 rows in no time and I would like to come up with a solution while I'm still under 5k rows. You will be a lifesaver if you help me solve this!! Any and all comments will be appreciated! -SG

7 Replies

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

If you post a sample sheet to work with we can better create a formula to help but I think you could use something like:
=ISNUMBER(XMATCH(D5, FILTER(D5:D10000, AB5:AB10000="Not Loaded","")))

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

Partial Comm Sample to Post

Let me know if this link doesn’t work. I’m on my phone and I don’t see anywhere to upload an attachment of the partial spreadsheet. I will check again when I get home to see if I can add an attachment. Can you see the screenshot? Thank you for attempting it!

best response confirmed by ShainaDay88 (New Contributor)
Solution

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

Add this conditional formatting in second position

=COUNTIFS(\$D\$5:\$D\$9;\$D5;\$AB\$5:\$AB\$9;"Not Loaded")

Adjust value 9

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

Try @Hecatonchire solution. I over complicated mine.

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

I could not get this formula to work in conditional formatting - but I added a column with the COUNTIFS formula to return a value. Then, I added a new conditional formatting rule so if any value in the COUNTIFS column AD is greater than 0 - highlight it. Thank you so very much! I spent a ridiculous amount of time trying to figure this out!

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

@ShainaDay88  appears to work.  had to adjust the columns a bit:

# Re: Excel - Nested formulas in conditional formatting - Nightmare!

I deleted my original formatting rule and it's working properly. Thank you so very much for your help! Made my day!