Find a cell with False 1

[sxschech]

I have a column with formula that evaluates to [tt]True[/tt] or [tt]False[/tt]. I wanted to use the Find (Ctrl+F) to locate a row with [tt]False[/tt]. It kept telling me that it could not find [tt]False[/tt]. My initial search on the web suggested things like adding another col, filter for false, use conditional formatting. Two reasons that I didn't want to filter or conditional formatting: (1) Did not want to complicate things if I pass this file on to someone else and have to show them how to set up conditional formatting as is much easier to use the Find box and (2) I needed to see the rows above and below the False and using the filter would "hide" those rows requiring me to unfilter and refilter multiple times. I ended up using conditional formatting so I could at least scroll down and see the row based on the cell being colored. Today, took another stab at searching and found the answer at

https://excelribbon.tips.net/T001786_Finding_Boolean_Values.html

and it suggested to change the "Look in" from Formulas to Values. Once I changed it to values, I was able to quickly go to a row containing False. Another article from the same author

https://excel.tips.net/T008801_Changing_Default_Search_Settings.html

mentions that the default find settings are reset when you exit Excel, so if user forgets to change back to formulas, at least when they restart excel, it will be back to "normal".

 

[SkipVought]

Hi,

In the Find Window change Look In: to VALUES.

I actually posted before reading your entire tome.

Users need to be made aware of Excel features. This optional feature takes an option change.

Why are users looking for FALSE?

I’d say that using a FILTER woukd be better. ALL such cells are in view.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sxschech]

Hi Skip,

Reason I didn't want to use filter is:

(2) I needed to see the rows above and below the False and using the filter would "hide" those rows

I wrote a formula to sort and look at gaps in sequence numbers that uses a regular expression to extract a number pattern from a list of file names. The false indicates that there is a "potential" gap between the row that shows false and the one above it and not that the row containing false has a problem. If I filter for false, I would not see the row above it (I suppose I could do an F5 to see the cell above, but that is an extra step too) in order to evaluate whether there is a gap or not as some gaps are apparently valid.

[tt]Logic + Exception = Visual Inspection[/tt]

 

[SkipVought]

You formula ought to return a FALSE if either THIS row OR the PREVIOUS row meets the criteria. Then a Filter could be used.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sxschech]

The intent/attempt of my original post was as a simple method to find a cell containing TRUE or FALSE, for when doing a quick search for something and not needing to add/modify formulas to the existing sheet such as when receiving someone else's spreadsheet etc. However, since we seem to be going down the path of chaning the thread from a tip to a question, here is the formula I was using. What modification would be needed to do as you suggest?

=IF(OR(L11=J11,J10=""),TRUE,IF(I11=I10,L10=J11,IF(ISNUMBER(VALUE(RIGHT(I11,1))),L11=J11,L10=J11)))

If you need data sample or the other cols' formula leading up to the above, I can post that too.

 

[SkipVought]

A representative sample of data would be great.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sxschech]

Ok, here is a file with sample data where I changed some of the file name wording. Since uploading an xlsx file, it does not include the regex macro, so I hard coded the values that were derived from the File Name in Column C. I did not come up with the Directory or File Names, they already existed when I was given the assignment as I wouldn't use all those dots dashes pound signs etc. Tab1 contains the original list and tab 2 contains the Sorted List (before final edits) with formulas.

Tab2 contains the data with the added formula cols.


I highlighted items in green on the second tab as those turned out not to be actual gaps in sequence, this was a case of incorrect file name. On Tab1, they appear to be sequenced properly if looking only at "-012" thru "-028". However when sorted by the directory and then by List Number they float to the top because that sort includes the 906- and 909-. By opening the actual files, I determined that the ones in green actually are in 909 and not 906, so once renamed, they sequence properly without gaps. Would your methodology be able to display the data to be able to make this determination that the issue is gap as well as those with typo rather than a gap?

 

[SkipVought]

In column K I put a formula to be used for filtering on TRUE...
[tt]
K3: =OR(AND(NOT(H3),NOT(ISBLANK(H3))),AND(H3,NOT(H4),NOT(ISBLANK(H4))))
[/tt]

Your workbook modified as stated above...

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sxschech]

Hi Skip, I'll take a look when I'm in the office tomorrow.

 

[SkipVought]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sxschech]

Thanks Skip. Couldn't log in on Friday for some reason. Monday a holiday.