Controlling Stock Level

[Pheng]

I am wanting to create a spreadsheet that will allow me a visual coun
on the level of stocks I have.

I want to nominate an intended level of a product, have a colum
showing items on back-order and also another column showing items i
transit.

I want it to display a message saying:

• I need to order x qty of a product because it is below the intende
level.
• Display a message saying no order is needed due to being at th
intended level.
• A message showing a qty over the intended level.

I guess the two columns (transit and back order) will need to b
considered to allow the appropriate message based on the intende
level.

Any assistance is greatly appreciated

 

[JulieD]

Hi Pheng

Assume
A B C D
E
1 Level Stock on Hand B/O Transit
Order Status
2 100 50 20 0
=IF(sum(B22)<A2,"Order " & A2-SUM(B22),IF(SUM(B22)>A2,"More than level
" & SUM(B22)-A2,"No Order Necessary"))

Hope this helps
Cheers
JulieD

 

[Pheng]

Hey thanks, Its working great.

But can I have it so that every "if" statement displays a particula
colors too.

Cheers

 

[JulieD]

Hi

this will need to be set up in conditional formatting
- you can set three conditions per range here, so you're okay using this
method as long as you don't add a fourth condition (it can still be done,
but differently)

click on (as per the last example) E2 choose
format / conditional formatting from the menu
choose from d/d box
Formula Is
and type
=SUM(B22)<A2
click on the format button & set the formatting - click OK
Click on ADD button type
Formula Is =SUM(B22)<A2
click on the format button & set the formatting - click OK
Click on ADD button type
Formula Is =SUM(B22)=A2
click on the format button & set the formatting - click OK
Click on OK again

Let us know how you go

Cheers
JulieD

 

[Pheng]

Hey that done the job!

Thanks a million.

How can I rely on you again for future needs? Is there a direct emai
that I can ask help for in the future?

Help like you are hard to find, just look at the feedback, there's onl
you whom walked me through this functionality.

Again thanks

 

[JulieD]

Hi

glad to help ..

..not sure why no one else answered - you explained your question well and
most posts like this would have a heap of answers, must have got lost in the
muddle

my email address is (e-mail address removed) (without the remove this
bit) ... but its best to ask your questions in the group because there's an
awful lot of people in here who know an awful lot more than me ... so you
have a better chance of getting the best answer if you post it on the ng
rather than direct to me.

Cheers
JulieD