Returning a value from color coding and totals

kolonel

New member
Joined
Aug 9, 2012
Messages
6
Reaction score
0
Points
0
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

New member
Joined
Aug 9, 2012
Messages
6
Reaction score
0
Points
0
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

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.

All help appreciated.

Regards
Rob

View attachment Book1.xlsx
 

kolonel

New member
Joined
Aug 9, 2012
Messages
6
Reaction score
0
Points
0
Have had some help from another forum, but thought it would be good to share.

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.

Was hoping there was 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) ?
 
Top