Returning a value from color coding and totals

**kolonel** · 09-20-2012, 08:03 PM

Hey all,

Have been working on a spreadsheet that is slowly evolving, and was hoping i could extract information to make it even more automated, so to speak.

Currently, i receive 1 worksheet 2-3 times a week (quantities change regularly), that outlines about 8 different models (all on the same sheet) with quantities either sent, due to arrive, or in production. For each model, I have the sent items color coded blue, stock due colored yellow and the stock in production is left clear. I want to create a new worksheet which will take the information for each model, and setup to have it :

Total Sent Total Due Total in Production Total

Model #1
Model #2
Model #3
Model #4
etc.
etc.

Am i getting a little over my head expecting this, as i am still a novice in Excel. Is there an easier way to do this ?

Thanks for any and all help.

Regards
Rob

**kolonel** · 09-23-2012, 08:12 PM

Have attached a sample of how it looks on the spreadsheet, and was hoping to set it up that when the "Promise date" was more than 14 days, it would highlight (maybe add another column for overdue) red to make it easier for a visual over the sheet.

Book1.xlsx

All help appreciated.

Regards
Rob

**FDibbins** · 09-23-2012, 08:36 PM

try using conditional formatting. high-light the range you want (E2:E9?), on the home ribbon, select CF, select "format only cells", select "cells" (this should be the default anyway), select "less than" from the next pull-down, in the next empty boxe, enter =TODAY()-14. select "format" and select the fill you want, then enter twice. this will high-light all dates that are 14 days older than todays date.

if you want to have the flexibity to change the 14 day criteria, put it in its own cell, and then reference that cell in the above formula, instead iof using "14"

let me know if this is what you wanted, and how you made out please?

**kolonel** · 09-23-2012, 10:25 PM

Hey FDibbins, you're a gun. Thank you very much.

To take this one step further, is there a way to only "check" this criteria to those cells that are not already colored green, blue or yellow. That is, only to apply to those cells that are clear (in manufacturing) ?

Dont ask for much eh

**FDibbins** · 09-23-2012, 11:40 PM

you ca add multiple criteria under CF. why not use them for colorin the other cells you want to have colored? play around with the different options when you add a new rule, 1 i find most useful is "use formula"

and thx for the star