+ Reply to Thread
Results 1 to 10 of 10

Inventory Spreadsheet needs tweaking but I need help PLEASE

  1. #1
    Registered User
    Join Date
    07-16-2015
    Location
    Cheltenham
    MS-Off Ver
    Vista
    Posts
    4

    Cool Inventory Spreadsheet needs tweaking but I need help PLEASE

    Hi All,

    I need to control and keep a record of Stock items for a small Company I have just started working for.

    I've used a Template and made some adjustments, but I have no idea how to use formula's and Functions etc

    Some were on the Template, but I need to set min/max stock levels which then show qty to re order.

    I've attached the spreadsheet in the hope that someone can kindly help me..

    Thank you for taking the time to look

    J.
    Attached Files Attached Files
    Last edited by javley; 08-30-2015 at 12:27 PM. Reason: SOLVED

  2. #2
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    All you need is an IF statement to compare the min stock against the current inventory something like...

    Min stock levels in column J

    Then this in column L

    =IF(I2<=J2,"Reorder","")

    Windy

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Little bit confusing, what you actually want? Expected result with criteria should be clear for proper solution.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    05-16-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Need more information defining reorder point and min/max levels. It is better to check these links before preparing your spreadsheet.

    Reorder Point: (Average daily usage rate x Lead time) + Safety stock

    http://blog.lokad.com/journal/2009/1...-planning.html

    Fixed Quantity model (If link is broken search "ch15_inv_control_temp" xls file)

    https://www.google.com.tr/url?sa=t&r...1k5tAF-imb0eTQ

  5. #5
    Registered User
    Join Date
    07-16-2015
    Location
    Cheltenham
    MS-Off Ver
    Vista
    Posts
    4

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Hi Windy 58, Thanks for helping. I try to put = IF(I2<=J2, Reorder","") as you say into Column L, but nothing happens.
    Would you mind trying to put it into the spreadsheet in case I'm not doing it correctly please?

    Thanks very much

    J.
    Quote Originally Posted by windy58 View Post
    All you need is an IF statement to compare the min stock against the current inventory something like...

    Min stock levels in column J

    Then this in column L

    =IF(I2<=J2,"Reorder","")

    Windy

  6. #6
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Here you go. I have used your starting figures as minimum stock level, you can change this to suit.

    Windy
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-16-2015
    Location
    Cheltenham
    MS-Off Ver
    Vista
    Posts
    4

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Thanks again Windy.

    All good now.

    If I may be cheeky, can the Re order column also do a clever trick by suggesting the amount to reorder against my set stocking levels?

    For example if the Min stock = 2, and the Max stock = 25, and my stock went down to 1, then it would update and advise me to order 24 to bring up to the max stock of 25. If that makes sense?

    No worries if not as you have already been a star, but this would really be useful.

    Many thanks (again!)

    J.

  8. #8
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Try this...

    =IF(A2="","",IF(I2<=J2,"Reorder " &"("& K2-I2 &")"&" "& A2,""))

    It will also return item number and qty required.

    Windy

  9. #9
    Registered User
    Join Date
    07-16-2015
    Location
    Cheltenham
    MS-Off Ver
    Vista
    Posts
    4

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Thank you so much, you have solved all my problems. This will be really useful.

    I think it's time I learned some Excel formulas...

  10. #10
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Inventory Spreadsheet needs tweaking but I need help PLEASE

    Glad to have been of some assistance.

    Windy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Inventory spreadsheet
    By nynative in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-18-2012, 09:52 AM
  2. [SOLVED] Help with an inventory spreadsheet
    By Bigtroutcatcher in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-01-2012, 05:14 PM
  3. spreadsheet cell tweaking
    By hawkeye48 in forum Excel General
    Replies: 1
    Last Post: 06-29-2011, 09:48 AM
  4. Help tweaking an EXCEL inventory list!
    By rayman121985 in forum Excel General
    Replies: 2
    Last Post: 05-19-2011, 11:08 AM
  5. Tweaking a macro that retrieves info from closed spreadsheet
    By kostas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2010, 07:49 AM
  6. Inventory SpreadSheet.
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2009, 03:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1