referencing conditional formatting

[David Turner]

Using XL2000, I have made a simple inventory/order workbook consisting of 2
sheets: Food Items and Order Sheet

Currently in J4 of 'Food Items'
=IF(AND(H4<>"",H4>=G4),"Reorder!","")
Copied down to J41
This results in the flag to reorder when inventory reaches a certain level

Currently in A6 of 'Order Sheet'
=LEFT('Food Items'!I4,1)*COUNTIF('Food Items'!J4,"Reorder!")
Copied down to A43
This tells me how many cases to order

Works as intended, but I would like something to dazzle others <g>

First I would conditionally format the items list in A4:A41 of 'Food
Items' to red to flag for reordering. That's the easy part.

Can I adapt my formula on 'Order Sheet' to recognize the changed color of
food items on 'Food Items'?
Something like this in A6 of 'Order Sheet'
=LEFT('Food Items'!I4,1)*COUNTIF('Food Items'!A4, Red)

 

[Felipe]

David,

Check Chip Pearson's site for ways to count and sum cells
based on formatting.
http://www.cpearson.com/excel/colors.htm

Regards,
Felipe

 

[David Turner]

Felipe wrote

Check Chip Pearson's site for ways to count and sum cells
based on formatting.
http://www.cpearson.com/excel/colors.htm

Thanks, but those Functions won't work on cells based on conditional
formatting. There's a link to those that will, but on seeing them, I
decided I didn't want to dazzle others that much <sigh>.

http://www.cpearson.com/excel/CFColors.htm