Conditional Formatting OR Filter?

I would like to create a sheet that highlights a cell based on the contents. The contents of the sheet will regularly be copy/pasted from a text log file into the sheet. The sheet I have attached here is the end result. The log file tells us 1) Does anyone have more than 2,000 files in their account and 2) Does anyone have a folder in their account beyond one level past "Mailbox"

I would like the sheet to highlight any row based on the rules below:

If any of the rows contain a number 2,000 or higher -- highlight the row in RED. I can't figure out how to do that when the cell contains something like that shown below.
Attributes: Type 2,0 Rules 0,0 Files 10 Folders 0

This one is also tricky. Some rows will contains something like the following:
34 kb [E:] ctompkins/Mailbox/WEDDINGS/12.5.19 Meredith Nanney/

It's OK to ignore everything up until the "Mailbox/" and whatever comes immediately after the second "/" in the row. If there is a third or more "/" in the row, that row also needs to be highlighted in RED.

Any takers???

Much thanks!

Joe

I'm not sure I understand the question, Dennis. I apologize. I'm only slightly above a newbie with Excel.

Originally Posted by Dennis7849

Do you want to identify "Attributes" rows specifically, that have a value 2000 or higher within it?

Select Cell A1, then the column Heading "A"
Enter these two Conditional Formats, in THIS order

=(MID($A1,FIND("Files",$A1,1)+6, FIND(" ",$A1,FIND("Files",$A1,1)+1)- FIND("Files",$A1,1)-1 )*1)>=2500
=(LEN($A1)-LEN(SUBSTITUTE($A1,"\","")))>3

then Copy & Paste across as many columns as you wish to highlight

this will highlight anytime the phrase "Files >=2500" appears or there are more than 3 / in the Path
(If 3 is not the correct number change the >3 to however many / are acceptable, it wasn't clear to me.)

I must be dense, sorry! (And thank you for your assistance). I get a circular reference. I click on cell A1, and then I've tried clicking on the column A header or holding CTRL and clicking the column A header -- I enter the two formulas, back to back in one long line in the formula bar and press Enter. I get a circular reference warning and "0" shows up in A1.

Originally Posted by carsto

Select Cell A1, then the column Heading "A"
Enter these two Conditional Formats, in THIS order

=(MID($A1,FIND("Files",$A1,1)+6, FIND(" ",$A1,FIND("Files",$A1,1)+1)- FIND("Files",$A1,1)-1 )*1)>=2500
=(LEN($A1)-LEN(SUBSTITUTE($A1,"\","")))>3

then Copy & Paste across as many columns as you wish to highlight

this will highlight anytime the phrase "Files >=2500" appears or there are more than 3 / in the Path
(If 3 is not the correct number change the >3 to however many / are acceptable, it wasn't clear to me.)

you're not entering this formula into cell A1, you're entering a Conditional Format.
You're simply selecting A1 then selecting (DO NOT HOLD DOWN CTRL!) the column heading so the conditional formatting is applied correctly.

TO apply conditional formatting:
Edit > Conditional FOrmatting
under condition 1 use the drop down to select "formula is"
then copy and paste the first formula above
then click format and select your "red" formatting
click Add and a second Conditional Formatting will appear
under condition 2 use the drop down to select "formula is"
then copy and paste the first formula above
then click format and select your "red" formatting

WELL Your profile states you're using XL2003 but your file extension said 2007 or 2010! The above instructions are for 2003.

attached is your workbook with the Conditional Formatting applied to Columns A and B
Audit Worksheet temp.xls

I wasn't aware of the profile info -- I'm actually using 2010 (and I've updated the profile), thanks for letting me know.

In the sheet attached it seems to have highlighted some things but not dynamically and not necessarily the right content. For example, if I add "5000" to a cell in column A, it doesn't change the fill color. If I add 10 more "/" to a cell in column A, it doesn't change the color.

I'll keep testing it.

Originally Posted by carsto

you're not entering this formula into cell A1, you're entering a Conditional Format.
You're simply selecting A1 then selecting (DO NOT HOLD DOWN CTRL!) the column heading so the conditional formatting is applied correctly.

TO apply conditional formatting:
Edit > Conditional FOrmatting
under condition 1 use the drop down to select "formula is"
then copy and paste the first formula above
then click format and select your "red" formatting
click Add and a second Conditional Formatting will appear
under condition 2 use the drop down to select "formula is"
then copy and paste the first formula above
then click format and select your "red" formatting

WELL Your profile states you're using XL2003 but your file extension said 2007 or 2010! The above instructions are for 2003.

attached is your workbook with the Conditional Formatting applied to Columns A and B
Attachment 147141

Originally Posted by jhiltabidel

I wasn't aware of the profile info -- I'm actually using 2010 (and I've updated the profile), thanks for letting me know.

In the sheet attached it seems to have highlighted some things but not dynamically and not necessarily the right content. For example, if I add "5000" to a cell in column A, it doesn't change the fill color. If I add 10 more "/" to a cell in column A, it doesn't change the color.

I'll keep testing it.

1. it looks for the phrase "Files 5000", not just 5000, because it seemed you wanted the Files count >2000
as in the rows containing: Attributes: Type 2,0 Rules 0,0 Files 5000 Folders 0
otherwise it would make red a cell containing: Path: E:\FCNS\4513 and I didn't think you wanted that. If you do, let me know.
It doesn't appear that anyone has >2000 Files


2. It doesn't look for "/" [forward slash] it looks for "\" [backward slash]
Oops it should be looking for "/"!

Try the attached.

If still not correct, please highlight a sampling of the items you wish to be highlighted.

I think you nailed it! It's working great, thank you so much for that -- I modified it slightly to show files over 2,000 instead of 5,000 -- it's fantastic and for me, complicated. Thanks for taking the time!

Originally Posted by carsto

1. it looks for the phrase "Files 5000", not just 5000, because it seemed you wanted the Files count >2000
as in the rows containing: Attributes: Type 2,0 Rules 0,0 Files 5000 Folders 0
otherwise it would make red a cell containing: Path: E:\FCNS\4513 and I didn't think you wanted that. If you do, let me know.
It doesn't appear that anyone has >2000 Files


2. It doesn't look for "/" [forward slash] it looks for "\" [backward slash]
Oops it should be looking for "/"!

Try the attached.

If still not correct, please highlight a sampling of the items you wish to be highlighted.

Just out of curiosity and believe me, this would just be incredible gravy on top -- is there any way to also automatically filter the ones that fit the conditional formatting? So it only shows the results that are colored? If not, no big deal... this is still great.

That would take some thoughts on a macro.
I would think you would want to see all 3 rows related to the highlighted row, which complicates things.
My first thought is to build a helper column and filter on that.
Let me ponder this a while.

The highlighted row is really the only thing we need to see, not the other 2 -- I was thinking if an "X" could be placed in column F for any item that was highlighted - just using the filter drop down and showing only rows with an "X" would work just fine. Thoughts? I just don't know how to write a formula that is based on whether or not conditional formatting has been applied to another cell

Originally Posted by carsto

That would take some thoughts on a macro.
I would think you would want to see all 3 rows related to the highlighted row, which complicates things.
My first thought is to build a helper column and filter on that.
Let me ponder this a while.

Place this formula in F1 and copy down:
=IF(ISERROR(MID($A1,FIND("Files",$A1,1)+6, FIND(" ",$A1,FIND("Files",$A1,1)+1)- FIND("Files",$A1,1)-1 )*1),IF(ISERROR( (LEN($A1)-LEN(SUBSTITUTE($A1,"/","")))>3),"",IF( (LEN($A1)-LEN(SUBSTITUTE($A1,"/","")))>3,"X","")), IF((MID($A1,FIND("Files",$A1,1)+6, FIND(" ",$A1,FIND("Files",$A1,1)+1)- FIND("Files",$A1,1)-1 )*1)>=2500,"x",""))

then you can filter on F = "x"

I noticed it won't find results above 10,000 -- but I do not see that number referenced in any formula you've provided -- do you know why it might omit the results? For example, "Files 9999" gets highlighted but "Files 10001" is omitted.

Originally Posted by carsto

Place this formula in F1 and copy down:
=IF(ISERROR(MID($A1,FIND("Files",$A1,1)+6, FIND(" ",$A1,FIND("Files",$A1,1)+1)- FIND("Files",$A1,1)-1 )*1),IF(ISERROR( (LEN($A1)-LEN(SUBSTITUTE($A1,"/","")))>3),"",IF( (LEN($A1)-LEN(SUBSTITUTE($A1,"/","")))>3,"X","")), IF((MID($A1,FIND("Files",$A1,1)+6, FIND(" ",$A1,FIND("Files",$A1,1)+1)- FIND("Files",$A1,1)-1 )*1)>=2500,"x",""))

then you can filter on F = "x"

Copy of Audit Worksheet temp 1.xls
here's a corrected version. sorry

and sorry I didn't notice your post sooner.


I jacked with your data, so only use this for the conditional formatting and the formula in F
;-)