Multiple Cells Conditionally Formatting a Particular Cell?

[IsABatty]

So I'm using a 2011 version of Excel on Mac OS 10.7 and I wanted to highlight a cell with yellow using the condition that two other cells in a different row have matching values (namely "Amount in Shed" <= "Reorder Amount"). This works fine using the formula "=IF(J8<=L8,1)".

Now, each row represents a particular item in our inventory and this one cell in question is essentially just a status square that would be yellow any time ONE OR MORE items in our ENTIRE stock is at or below the reorder amount.

My question is: is it in fact possible to get the one cell to turn yellow (in this case) when ANY row(s) has fulfilled the condition? If so, how would i do it?

Thanks

 

[RonB1111]

Try this Conditional Format Formula:

=SUMPRODUCT(--(J8:J25-L8:L25<=0))>0

Adjust the ranges to your actual data

 

[IsABatty]

Doesn't seem to be working. It might help if made a facsimile of my sheet.[TABLE="width: 1000, align: center"]
<tbody>[TR]
[TD]ORDER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*Cell to be colored*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][Merged cell with inventory super category name, e.g. "Adhesives"][/TD]
[TD]<--MERGED[/TD]
[TD]<--MERGED[/TD]
[TD]<--MERGED[/TD]
[TD]<--MERGED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][Name, eg. "Liquid Hardener"][/TD]
[TD][retail price][/TD]
[TD][current amount][/TD]
[TD][unit][/TD]
[TD][reorder amount][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][Merged cell with inventory super category name, e.g. "Blades"][/TD]
[TD]<--MERGED[/TD]
[TD]<--MERGED[/TD]
[TD]<--MERGED[/TD]
[TD]<--MERGED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][Name, eg. "4.5 inch segmented"][/TD]
[TD][retail price][/TD]
[TD][current amount][/TD]
[TD][unit][/TD]
[TD][reorder amount][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The bolded cells within the rows are those that when equal, will turn that whole row red (to signify the need to order more). Hopefully that's not confusing, but basically I'm trying to show how 'current' and 'reorder' cells AREN'T ADJACENT, and neither are all of rows to each other because of the merged super category cells which separate particular groups of rows.

Any ideas?

 

[RonB1111]

Sorry, I misunderstood what your were originally requesting. Thank you for clarifying.

I've made these assumptions now. You'll need to adjust the formula or selected column range if your actual data is different.
1. "Current amount"is in Col J
2. "Reorder amount" is in Col L
3. If Current is <= Reorder, then color entire row.

Select Columns A thru L and use this conditional formula:

=AND($J1<=$L1,ISNUMBER($J1))

If this doesn't do what you want, when you post back please also identify row and column numbers where your data is.

 

[IsABatty]

That's fine, I probably wasn't being sufficiently clear. However, I've presently formatted the rows such that they turn red when 'current' is less than or equal to 'reorder', using the same formula you supplied. That works fine

My next objective is to get that cell under 'ORDER' (marked with asterisks above) to turn yellow when any of the rows have turned red due to the aforementioned formatting.

Is that possible given the set up of my sheet?

Thanks

 

[RonB1111]

The 1st formula I gave will do that. Just change the row range of 8-25 with the actual range your data is in.
For that cell only, remove the Conditional Formatting that's currently there and add a new rule with the 2nd formula.

 

[RonB1111]

The 1st formula I gave will do that. Just change the row range of 8-25 with the actual range your data is in.
For that cell only, remove the Conditional Formatting that's currently there and add a new rule with the 2nd formula.

Just noticed your data has some partially blank rows with the merged cells, so I've adjusted the formula to account for that:

=SUMPRODUCT(--(J8:J25-L8:L25<=0),--(J8:J25<>""))>0

 

[IsABatty]

Just so I understand: if the data in question go from (e.g.) A8 to A11 and L8 to L11, then I should modify the formula like so

=SUMPRODUCT(--(A8:A11-L8:L11<=0),--(A8:A11<>""))>0 ?

Also, in that formula, are we trying only to reference the two cells that are used in a row's conditional formatting ('current' and 'reorder') or the entire row (which turns red)?

Incidentally, the formula as modified above is not currently working properly

 

[RonB1111]

If the Current Amt is still in Col J and the ReOrder Amt in Col L, and the range you want colored if it Current < = ReOrder, change the formula to:

=SUMPRODUCT(--(J8:J11-L8:L11<=0),--(J8:J11<>""))>0

If the Current Amt column was in Col H, change the 2 instances of J8:J11 to H8:H11
If the Reorder Amt column was in Col K, change the 1 instances of L8:L11 to K8:K11

Here's how you input this conditional formula:
1. Select A8:L11
2. On the Home Ribbon, click Conditional Formatting - New Rule
3. In the window that pops up, click "Use a formula to determine which cells to format"
4. Copy/Paste the formula into the box immediately below the words "Format Values where this format is true"
5. Click the "Format" button which is in the lower right corner of the window (just above the cancel button)
6. On the window that pops up, click on the Fill tab, then click the Yellow color, then click OK
7. Click OK

 

[IsABatty]

So that formula worked but it ended up modifying the wrong cell. I tried it in the order status cell (A3) and the cell did not change color, even though the conditions for some rows in the A8:L11 range have been met.

I have attached a screenshot of the sheet and circled the cell in question. Given the complexity of your answer, I'm confident there's a more elegant solution other than the crude one I'm considering (which is to simply insert a column which would add a cell to the end each row and input a '1' if the row is red, a '0' if it isn't, then have the order status cell turn yellow if the sum of those new column is more than 0). I feel like it could all be done with one formula in cell A3.

Thanks for your patience and help