Help needed, setting up conditions

  • Thread starter Thread starter Uncle Vinnie
  • Start date Start date
U

Uncle Vinnie

I am not exactly sure how to word this, I'll try my best.. I know I've come
to the right place, that's for sure!

I have been given a spreadsheet - as follows:

First column (A) is a list of locations.
Column B, C, D, E, F, & G are 6 individual items.
Down each of these columns are year to date sales of each of these 6 items,
which correspond with each location listed in column A.


The next 6 columns would show the remaining inventory of each item, at each
location.

I would like to set up a third set of columns that would alert me when
inventory falls below a certain point- perhaps when it falls below half of
sales.

Since each item comes in a different case pack, each column's quantity would
change however this is where the percents would help...

Any suggestions and advice would greatly be appreciated!

Thank you!
 
To add to this:

I guess what I am trying to accomplish is to compare sales of widget'x' in
column b, with the remaining inventory of that widget'x', which should be
listed in Column H.

Make sense?

thank you!!
 
Try this

In Columns N through S

Enter the first formula in the first cell in N, my example will use N2

=H2-B2

Copy and paste this troughout columns N through S

And you can do anything to notify you when an inventory meets your
requirement. One way would be to set conditional formats. For example

Select the columns B through G

go to Format---> Conditional Format

And change Cell Value is to Formula is and put =N2>2

Then change the format to anything you want. This says for all values
through N and S, if the value is over 2 it will highlight or change the
format to whatever you select. 2 is just an example... use it however you
want. Theres 3 conditions you can base your facts off. Just remember the
formula is has to relate to the active cell in order for it to populate the
rest of the active cells. So start your highlighting in cell B2 or wherever
your data starts. This is a start, let me know if you have any other
questions.
 
Hi.. I wanted to acknowledge and thank you for this!

I have part but not all figured out.. (meaning- you really know your
stuff!).

I see that the first column would in effect reflect inventory less sales.. I
need to understand then what it is I am looking for, as well as what you
suggest to implement.

Each item comes in a case pack... some are 72 pcs, some are 36.

If sales of item X, which comes in a 36 pc case pack, are 10 pieces, then
inventory (which often never matches!), should show 26 pcs. 10-26 should be
(16).

I think I want to set up a parameter that if there is less than 33%, the
item should be reordered... some locations go through much more than
others..
so then I have to determine if they get 36pcs, 72 pcs, etc...

See where I am going??

Thank you for your help!!!
 
I see it, I like it, it works!

Is there a way to format conditionally as follows:

If a certain cell is less than a certain number, then make it a new number?

Example:

If inventory level falls below 18 pcs, then make that number a '36'.

If it is above 18 pcs, then make the number '0'?

What I am doing would be creating a reorder sheet based on the remainign
inventory!
 
Back
Top