G
gcotterl
How can I mark rows that do NOT have a value (for example: 04-5498) in
selected columns (for exampe: A, D, G, K)?
selected columns (for exampe: A, D, G, K)?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Max
Perhaps you'd like to color these cells?
If so, try conditional formatting
Select col A
Click Format > Conditional Formatting
Under Condition 1:
Formula is| =A1=""
Click Format button > Patterns tab > light brown? > OK
Click OK at the main dialog
Now to propagate the cond formatting:
With col A still selected, *double-click* on the Format Painter icon (brush)
- cursor will turn into a brush
Click on the col headers: D, G, K, etc to paint/copy the conditional
formatting over
(Excel will auto-adjust the formulas relatively to refer to the col)
Press Esc when done to revert cursor to normal
Any empty cells in the cols conditionally formatted, or cells with formulas
which evaluate to null: "", will be colored with light brown fill.
If so, try conditional formatting
Select col A
Click Format > Conditional Formatting
Under Condition 1:
Formula is| =A1=""
Click Format button > Patterns tab > light brown? > OK
Click OK at the main dialog
Now to propagate the cond formatting:
With col A still selected, *double-click* on the Format Painter icon (brush)
- cursor will turn into a brush
Click on the col headers: D, G, K, etc to paint/copy the conditional
formatting over
(Excel will auto-adjust the formulas relatively to refer to the col)
Press Esc when done to revert cursor to normal
Any empty cells in the cols conditionally formatted, or cells with formulas
which evaluate to null: "", will be colored with light brown fill.
G
gcotterl
How do I find the rows that do NOT have "04-5498" in ANY column?
M
Myrna Larson
In a Helper column on the right
=IF(COUNTIF(A2:Z2,"04-5498")=0,"X","")
and copy down through all rows. Those without the target text will have an X
in this column.
=IF(COUNTIF(A2:Z2,"04-5498")=0,"X","")
and copy down through all rows. Those without the target text will have an X
in this column.
G
gcotterl
Myrna,
Shouldn't the range be =IF(COUNTIF(A1:Z1,"04-5498")=0,"X","")
instead of =IF(COUNTIF(A2:Z2,"04-5498")=0,"X","")
Gary
==========================================
Shouldn't the range be =IF(COUNTIF(A1:Z1,"04-5498")=0,"X","")
instead of =IF(COUNTIF(A2:Z2,"04-5498")=0,"X","")
Gary
==========================================
M
Myrna Larson
It can be whatever range the you are interested in. What I posted is an
example. The user must change the references to the row to be checked. If the
worksheet contains a list, the first row is probably a header row, and the
formula would not be relevant there.
example. The user must change the references to the row to be checked. If the
worksheet contains a list, the first row is probably a header row, and the
formula would not be relevant there.
M
Max
gcotterl said:How do I find the rows that do NOT have "04-5498" in ANY column?
Just in case you're still interested in the CF approach ..
(apologies for the earlier mis-interp <g>)
Select the rows to be covered, say rows 1 to 10
(click and drag on the row headers 1 to 10)
Click Format > Conditional Formatting
Under Condition 1:
Formula is| =COUNTIF(1:1,"04-5498")=0
Click Format button > Patterns tab > light brown? > OK
Click OK at the main dialog
(Excel will auto-adjust the formulas relatively)
If there's no cell within any row (in the formatted range)
that contains: "04-5498"
then the entire row will be colored with light brown fill
Test it out by inputting*: "04-5498" in any cell in any row which already
appear conditionally formatted with light brown fill. The fill color for the
entire row will disappear.
*Precede the input with an apostrophe (')