Extrapolating Conditional Formatting

[Dan Wilson]

Good day. I am using Excel 2002 on Win XP Pro. I have a workbook that audits the sales of sponsor flags for a golf tournament. There are currently 164 rows of data in the workbook. Column L contains the sponsor name and Column M contains a blank if the sponsorship is not yet paid and an "X" if it is paid. The user wants to be able to see at a glance which sponsors are still unpaid. Obviously, the answer is to view Column M. The user wants me to make the sponsor name (Column L)come up in a Pattern of yellow if unpaid to make it easier to find.

I have created the Conditional Formatting using Formula Is and all is well.

Is there a way to extrapolate the conditional formatting from one row to the next and have the formula automatically adjusted to look at Column M in that row? Otherwise, I will have to insert the conditional formatting into all of the rows at once and then edit the conditional format in each row to point to that row.

Thanks for any help.
Dan...

 

[barry houdini]

You can achieve this in one step.

Select column L

Use this formula in conditional formatting

=(L1<>"")*(M1="")

select yellow formatting

The formula will automatically adjust for each row

 

[Dan Wilson]

Reply to Barry

Good day Barry,

Wow! I don't understand how it works, what it is doing or why, but it works! Thank you.

Dan...

 

[Scott Huish]

Numerically speaking, 0 evaluates as FALSE and non-zero evaluates as TRUE.
Doing math with a logical (Boolean) expression on the spreadsheet coerces TRUE to 1 and FALSE to 0.

If both conditions are true, this would evaluate as 1*1=1 or TRUE
if either condition is false, this would evalute to 1*0=0, 0*1=0 or 0*0=0 or FALSE

Conditional formatting formats when the expression evaluates to TRUE which 1 does.

 

[Dan Wilson]

Reply to HotPepper

Good day Hotpepper. Thanks for the response. After I thought about it for a while, I see what is happening. What a simple process! I will remember this in the future.
Thanks again for taking the time to respond.
Dan...