Multiple conditions

[destiny4adams]

I am trying to build a spreadsheet that has multiple condition. When I plug in my conditions into conditional formatting, it won't work.

1st condition: I want 3 rows to be filled in blue, with border and vertical lines highlighted, one line no fill/border, then the next 3 rows to be no fill, with border and vertical lines.
2nd condition: If in column D there is the word Refund or Refunded then it is filled with green, with border and vertical lines.
3rd condition: If certain numbers in a text is used in column H then column G has a certain word in the first line of the previous conditions.
4th condition: If there are no numbers in column H then column G is blank in the first line of the previous conditions.
5th condition: If there are ( ) in column F then row 2 in column D say's REFUNDED in bold.
6th condition: If the first line of the section in column E has Agent Fee then the first line (3 line sections) says NT Agent Fee
7th condition: If the first line of the section in column E has a name of an airline in it (Delta, Southwest, United, American, Spirit, etc.) then the first line (3 line sections) says National Travel.
8th condition: If there conditions 6 and 7 are not in column E then column D is blank.

On G2 of my snippet I used this code in the cell, but I know there is a better way to do this.

=IF(ISNUMBER(SEARCH("024003",H6)),"2024 Initial Attack",IF(ISNUMBER(SEARCH("024002",H6)),"2024 Aviation Support", IF(ISNUMBER(SEARCH("240324",H6)),"Windy Deuce", IF(ISNUMBER(SEARCH("240317",H6)),"
Smokehouse Creek",AND(ISBLANK(A1), ISBLANK(B1),""))))

 

[awoohaw]

Would you be kind enough to use the xl2bb add in and post a mini worksheet of your data? If you cannot post that, then please post your data as table (also giving formulas if there are any, also labeling the row and column numbers and letters). Posting images means the forum must completely reconstruct your scenario.

One way to work toward constructing conditional formulas is to write boolean formulas in the columns to the right of your data.
Since you have 8 rules, you'd create formulas in 8 columns. Each formula needs to resolve to TRUE.

Additionally, some of your requested CONDITIONAL FORMATTING conditions are formulas and not something for a CF rule.

 

[destiny4adams]

Would you be kind enough to use the xl2bb add in and post a mini worksheet of your data? If you cannot post that, then please post your data as table (also giving formulas if there are any, also labeling the row and column numbers and letters). Posting images means the forum must completely reconstruct your scenario.

One way to work toward constructing conditional formulas is to write boolean formulas in the columns to the right of your data.
Since you have 8 rules, you'd create formulas in 8 columns. Each formula needs to resolve to TRUE.

Additionally, some of your requested CONDITIONAL FORMATTING conditions are formulas and not something for a CF rule.

I have tried loading xl2bb and multiple ways following the xl2bb forum. This is the best that I have been able to come up with, and it doesn't have the options that you are needing. Can I email you the xlsx?

 

[awoohaw]

you can post the worksheet in reputable share drive (your one drive, dropbox, etc).
But the image does not help one bit. You can highlight the range, click copy. Come to the thread here, click inside a message box and paste.
But it iwll only post what is visible, not any formulas, you'll need to copy and paste those as well. (if the formulatext function is in 2016 excel, that may make sharing formulas easier).
also would need to the the cell reference address of the top left cell in the table you post.

(you could change your view of the range by clicking CNTL-tilde to display formulas instead of values.)

 

[destiny4adams]

you can post the worksheet in reputable share drive (your one drive, dropbox, etc).
But the image does not help one bit. You can highlight the range, click copy. Come to the thread here, click inside a message box and paste.
But it iwll only post what is visible, not any formulas, you'll need to copy and paste those as well. (if the formulatext function is in 2016 excel, that may make sharing formulas easier).
also would need to the the cell reference address of the top left cell in the table you post.

(you could change your view of the range by clicking CNTL-tilde to display formulas instead of values.)

Who do I share the file with?

 

[awoohaw]

in this thread. You would need to choose "anybody with the link can view" the file. Paste the link in a post here. It can be read only and forum would download it.

 

[destiny4adams]

Statement- my copy.xlsx

Statement1 # .,Post Date,Trans Date,Vendor,Description,Billing Amount,Incident Name,Incident Number 1,3/4/2024,3/2/2024,NT Agent Fee,AGENT FEE 89008856946796 NATIONAL TRAV TX, $ 6.50 ,2024 Aviation Support,TX-TXS-024002 Name XAA XD X XAO 2,3/4/2024,3/2/2024,National Tr...

docs.google.com

 

[destiny4adams]

Statement- my copy.xlsx

Statement1 # .,Post Date,Trans Date,Vendor,Description,Billing Amount,Incident Name,Incident Number 1,3/4/2024,3/2/2024,NT Agent Fee,AGENT FEE 89008856946796 NATIONAL TRAV TX, $ 6.50 ,2024 Aviation Support,TX-TXS-024002 Name XAA XD X XAO 2,3/4/2024,3/2/2024,National Tr...

docs.google.com

Did this link work?

 

[awoohaw]

Yes, link works. but are all of your scenarios located in this workbook? For instance, I cannot find any thing like "*REFUND*" in column D? Could it be in any of the 3 cells in column D for each record? How can we test out your requirements?