Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by cashback

  1. cashback

    Calculate the number of weeks the stock on hand will last

    To ETID, Any chance you could modify the code for the Function StockDepletedWeek to do what I have suggested. I've tried to do it myself but It's beyond my understanding. I'm willing to learn, if you can break the steps down for me. Happy to send you the sheet I'm using it in. Thanks Kevin
  2. cashback

    Calculate the number of weeks the stock on hand will last

    Better Idea (if possible) example: Col A = Item No. Col B = Inventory Col C = Demand Rows 2 through 41 have item 123 Rows 42 through 81 have item 456 If the inventory in row 36 = 600 and the demand in C36, C37, C38, C39, C40, C41 totals 500 the calc would not be able to determine the number of...
  3. cashback

    Calculate the number of weeks the stock on hand will last

    I think the best way is to have the figure you have added the the last row of col D appear in as the last record for each change of Item code in Col A. Given a specified number of rows for each item (40) in my case the item number in Col A changes at cell A42. I noted that the values in cells...
  4. cashback

    Calculate the number of weeks the stock on hand will last

    Hang On - Spoke too soon. Seems the calcs get a little strange as they run down the sheet. 7.8 vs 5.8 (seventh line) Example of Results Col 1 = Inventory Col 2 = Demand Col 3 = Result from new function Col 4 = Correct Answer 245 46 4.2 4.2 199 64 3.4 3.2 279 62 4.8 4.7 217 62 3.7 3.7 155 59...
  5. cashback

    Calculate the number of weeks the stock on hand will last

    Perfect... Works likes a charm. Thankyou..
  6. cashback

    Calculate the number of weeks the stock on hand will last

    Thanks, I was trying to get the result in cell D2 instead of the row where the demand for that week exceeds the demand from B2. It certainly returns the correct result but forces me to have multiple columns to enable the sheet to calculate each subsequent row of Inventory vs Demand in the same...
  7. cashback

    Calculate the number of weeks the stock on hand will last

    Thanks to all who tried to help. I'm going to try another approach which may simplify the formula but not the amount of fields I will need to accomplish the result. I'll make sure I explain any new threads more clearly in future. Cheers Kevin
  8. cashback

    Calculate the number of weeks the stock on hand will last

    Sorry Skip, I thought I made it clear but looks like I failed. My Fault not yours..Thanks for your trying. Kevin
  9. cashback

    Calculate the number of weeks the stock on hand will last

    Thanks again but still not there. As this information is used to plan purchases the aim is to never let the value in col(B) be less than at least 3 weeks demand from Column C. Hence counting how many rows the value from B2 is greater than the value in Col (C) is not quite correct. My aim was to...
  10. cashback

    Calculate the number of weeks the stock on hand will last

    Thanks for the replies.. ETID, I the formula you gave me gave me a blank cell as the result. I changed the formula to =IF(B2<C2,"0",IF(B2>0,ROUND(ROW()-2+B2/C2,1),"")) This gave me a result but it was more a calculation of the value in B2/C2 rather than what I'm looking for. I need the formula...
  11. cashback

    Calculate the number of weeks the stock on hand will last

    I need to calculate how many weeks my on hand stock will last based on the weekly demands going out 30 weeks (current limit of data for each item). Column A has the item code Column B has the On Hand stock (each row = 1 week) Column C has the weekly Demand Data example Cell B2 has 245 units as...
  12. cashback

    How to remove external data links via Macro

    Thanks to all that replied. I used the code from Skip and it works perfectly. No external links remain on the sheet. I now have a spreadsheet for vendor schedules which takes data from my AS400 sorts it by supplier number creates a seperate spreadsheet for each and automatically emails the...
  13. cashback

    How to remove external data links via Macro

    I need to be able to remove external data links from sheets that are generated via a macro. The resulting sheets need to retain the data but no links. Copy / Paste / Values does not work and I cannot record the steps into a macro when I tried : Select External Data Range Properties then removed...
See more

Part and Inventory Search

Back
Top