Querying text ranges

[Pyrex238]

I've been racking my brain for hours over the past few days trying to compile a spreadsheet for work. Currently the spreadsheet covers all the aspects of a auto loan process. It is used as a tool for auditors to check the work of others. The spreadsheet simply uses "P" to designate "Pass" or "F" to designate "Fail" on whichever section. How do I reference the P's and the F's in the spreadsheet? I've had trouble getting excel to query any text in cells. Also, each loan application is started on the next line, so I need to make sure these formulas repeat as a new line is started - how would I go about doing this? Currently - Cell G3-CG3 contain the "P's" or "F's" - the formula I had used was {=IF(G3<>"P","FAIL","PASS")} - which does work, on a single cell. However, if I try to add the entire range in {=IF(G3:CG3<>"P","FAIL","PASS")} the cell returns #VALUE. Obviously, I'm inexperienced with excel in this fashion, and I need to know what formula I should be using. I really really appreciate your input. Thanks very much!

[duane]

are you trying to test if the entire range of g3:cg3 is either a "p" or a "f"?

if so, one way

=if(countif(g3:cg3,"p")=counta(g3:cg3),desired result for p,etc

[Pyrex238]

are you trying to test if the entire range of g3:cg3 is either a "p" or a "f"?

if so, one way

=if(countif(g3:cg3,"p")=counta(g3:cg3),desired result for p,etc

beautiful! amazing! I'd kiss you if you weren't my e-hero. That worked fantastic. How do I make certain this formula will repeat for all column C3 and below as lines are added? thanks so much! You made my day!

-Also, is it possible to automatically format the P's to PASS green color, and the F's to FAIL red? so if the auditor input P it would format "PASS" in green text? I'm intrigued!

[duane]

if(countif($g$3:$c$g3,"p")=counta($g$3:$cg3),desired result for p,etc

when you enter an address such as c3, just hit f4 on your keyboard to change it to $c$3

[duane]

for your second question, on the cell in question select format, conditional formatting

[Paul]

If you're looking to be able to copy the formula down rows as rows are added, I don't think you want to be adding $'s in. That will make the formula static.

Also, the formula can be shortened to:

=IF(COUNTIF(G3:CG3,"F"),"FAIL","PASS")

You can then use conditional formatting on the column to change the colors when a cell is "PASS" or "FAIL" (Select those cells or the entire column, click Format->Conditional Formatting, set 'Cell Value Is' -> 'equal to' -> PASS, click Format button and change the font/background colors as necessary. Click the Add button and in condition 2 set 'Cell Value Is' -> 'equal to' -> FAIL, set colors, click OK twice to return to workbook.

[Pyrex238]

If you're looking to be able to copy the formula down rows as rows are added, I don't think you want to be adding $'s in. That will make the formula static.

Also, the formula can be shortened to:

=IF(COUNTIF(G3:CG3,"F"),"FAIL","PASS")

You can then use conditional formatting on the column to change the colors when a cell is "PASS" or "FAIL" (Select those cells or the entire column, click Format->Conditional Formatting, set 'Cell Value Is' -> 'equal to' -> PASS, click Format button and change the font/background colors as necessary. Click the Add button and in condition 2 set 'Cell Value Is' -> 'equal to' -> FAIL, set colors, click OK twice to return to workbook.

Okay, great.. that works much better. I did get the conditional formatting to work for me... my problem is I want all rows that are added on to keep the formula above.. which as of right now (with the $'s) does not happen. Same applies for the conditional formatting.. which apparently requires me to select all the cells I want effected, and not an infinite number of future rows.

[mikerickson]

That is normal behaviour. Insert will insert rows and format them similarly to the preceeding row, but filling them is left to the user.

[Pyrex238]

That is normal behaviour. Insert will insert rows and format them similarly to the preceeding row, but filling them is left to the user.

Here is an example:

\1

[mikerickson]

Instead of (or in addition to) adding rows, use fill down.

[Pyrex238]

Instead of (or in addition to) adding rows, use fill down.

how does that work when you want to fill infinitely? excuse my inexperience! If I use fill, it will display a green "PASS" cell all the way down my sheet, I was wanting the cell to appear once a new row once created... does that make sense?