Inventory Analysis

[Joliet Balimba]

I am trying to create an inventory chart for products that have a
certain shelf life. My chart would have four columns; location, sku #,
description and expiration date. Is there a way not only to have
computer automatically update based on actual date and is there a way
for computer to update colors, red=<30 days, yellow=<60 days, green=61
days or more. It would look kind of like:

today's date: 10/26/03

store sku description expiration

102 111-213 dirt 12/31/03

I would like these rows to turn red/yellow/green based on today's date
entered.

 

[Chris Leonard]

Is there a way not only to have computer automatically update based on
actual date

Not sure what you mean

is there a way for computer to update colors, red=<30 days, yellow=<60

days, green=61 days or more.

Take a look at conditional formatting, this will do exactly what you're
after.

 

[Maurice]

Try This
A1 = Store, B1 = SKU, C1 = Description, D1 = expiration,

G1 "=TODAY()" Formated as Date

Column D is also formated as in G1, extend as necessary

Column E "=D3-$G$1", the dollar signs are important to maintain the
reference, extend as for D

Then select a cell in E and Format>Conditional Format

Condition 1 Cell value between 0 and 30 (GREEN)
Condition 2 Cell value between 31 and 60 (ORANGE)
Condition 3 Cell value is greater than 60 (RED)

"The colours are those selected"

If you where then to select Row A1 you could then Data>AutoFilter, the
filters would the allow you to check for store, sku, expiration

I think as the day changes you either need to "F9" or close and open
the sheet which should do the updates.

Its quick and dirty and easy to play with