Inventory Analysis

  • Thread starter Thread starter Joliet Balimba
  • Start date Start date
J

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.
 
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
Click to expand...
days, green=61 days or more.

Take a look at conditional formatting, this will do exactly what you're
after.
 
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
 
Back
Top