You are here:

# Excel/Inventory decrease

Question
Hi Edward

I am creating a spreadsheet that will track stock levels

I have tried the below formula but it keeps returning the incorrect number

=INDEX(\$F\$3:\$F\$3,MIN(IF(D3:D3>1,COLUMN(F3:F3))))

I need it to do the following

D3          D4          D5          D6
Current Stock   Purchased   Remaining   Order
5          3          2          how many to order?

but when it falls below 3 in stock(order column) id also like it to colour the cell in red to alert me that new stock needs order ?

the formula needs to work on each line it is places to track individual products

The formula starts in d3 and moves across the page if that helps

so basically i need the formula to look at cell d5 and determine thats its fallen below the allowable stock limit of 3 and in d6 say i need to order however many to bring the stock level back up

Hope this all makes sense and i am not asking to much lol

Thanks
Marcus

It's a little difficult to see what you need to do via the medium of allexperts as the columns of your spreadsheet get corrupted by the text only nature.  For the colour question, you can use a conditional format to pick up (via formula) that the cell value is less than another cell value (i.e the one with the minimum amount). If the cell value is below the minimum, an IF statement would do it for you (in essence, if stock below minimum show the order quantity otherwise show blank).  Happy to help further - my direct email is aidan.heritage@virgin.net which would allow you to share a sample workbook with me, which would, I think, make it much easier to give you a valid and working solution to your problem.

Question found in question pool meaning the original expert was either unable or unwilling to answer the question.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Aidan Heritage

##### Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

##### Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!