Calculate inventory, plus or minus, in real time, with item usage

[TommyRoton]

On worksheet 1 (WS1) of a spreadsheet I want to calculate the need for one of several items, ItemA, ItemB, ItemC etc. If ItemA is required, it searches WS2 to see if it's in stock, (Item quantity is maintained on WS2 of the spreadsheet). As I use ItemA in cells A1, A2 and A3 on WS1, it reduces their quantity on WS2, conversely, if ItemA is freed up on WS1 because ItemB has now been calculated as required, it adds ItemA to the quantity in WS2 and reduces the quantity of ItemB. If I deplete the total quantity of ItemA on WS2, some sort of indicator on WS1 changes ItemA to red, prints ItemA Out Of Stock, Buy or some other such means. I'm thinking WS2 should have a Total, Used and Available column for the items, that changes dynamically as need is calculated on WS1.

Right now I'm using vlookup to find ItemA on WS2, once it's need is calculated, but it falls short of providing the info I'd like to have, as mentioned above.

I hope that's not to complicated a question, it is for me, but that's not saying very much?

Thanks much for any help one might be able to provide.


Tom

[mehmetcik]

As you say you will need a few columns for this to work.

In your Stock spreadsheet

Stock Available
Stock Committed
Stock Total
Stock in from suppliers or returned

On your Sales Spreadsheet you will have your stock required Column.

What you have not figured out yet is that you need a macro to

Add your Stock required to the Stock Committed Column and then clear stock required

Also a Macro to add Stock from Suppliers to Stock Available and then clear stock from suppliers.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[TommyRoton]

Thanks much, mehmetcik!

I'll set up the mock pages and attach to my next message as you requested. It'll probably take me a bit of time, as I'm rather slow, please be patient.

Again thanks for your time and energy!


Tom

[TommyRoton]

mehmetcik,

Sorry it took so long, This is a Linux machine, my Windows machine has no Internet access, had to use removable drives.
Also the math for that which I thought was sound, was not, so I had to rework it.

Attached, just below, is a basic sample.
MockSheet.xls


Thanks for any help you can offer.


Tom

[mehmetcik]

I am sorry but I don't understand.

B2 contains 2550 which appears to be a part number (Item No)
B3 contains a number that seems to be plucked out of thin air

B4 manipulates B2 + B3 and another constant ( Min B) to come up with another number 2500
B4 then looks up 2500 using VLookup and returns 2500

B5 is B2+B3-B4 ie 0.160 which is the new value of B??????????

B6 gives the present stock of the number in B4 which is not necessarily the Item No.

[TommyRoton]

I am sorry but I don't understand.

B2 contains 2550 which appears to be a part number (Item No)
B3 contains a number that seems to be plucked out of thin air

B4 manipulates B2 + B3 and another constant ( Min B) to come up with another number 2500
B4 then looks up 2500 using VLookup and returns 2500

B5 is B2+B3-B4 ie 0.160 which is the new value of B??????????

B6 gives the present stock of the number in B4 which is not necessarily the Item No.

No I'm sorry, that I failed to explain that the only two things I need help with are contained in the comment shapes (bubbles). All the rest is 100% perfect, that's what I had to fix before I sent you a copy. The yellow and red columns on the Inventory sheet are the only things I need help calculating, and those are the things you asked nothing about. I apologize again for being so vague.

First, I need your help calculating the "In Use" column on the Inventory sheet. Right now those numbers are arbitrary, (just for show), they need to be calculated values gotten from the Calculator sheets, row of cells "B4:E4."

For example: on the Inventory sheet, D3 now contains the arbitrary number (7), it needs to be the calculated running total of occurrences of item A2 (2.250) that appear on the Calculator sheet in row (B4:E4).

Second, The "Order" column on the Inventory sheet is exactly the same type problem, (yet a little more complicated because two rows have to be parsed), as the "In Use" problem. It must contain the running total of occurrences of zeros (0) for a particular item number, i.e., A3 (2.250) that shows up on the Calculator sheet: (B4:E4) as indicated on the Calculator sheet row (B6:E6).

For example: on the calculator sheet C6 shows that Item 2.550 is out of stock, in the full version "Cust M" and "Cust W" might also require a 2.550. That number of required stock should appear on the Inventory sheet (E9) (which currently has an arbitrary number (1) in it). But in this example it would contain a 3 indicating that I had to order 1 "2.550" each for "Cust B," "Cust M" and "Cust W." A total of 3.

Know that on the Inventory sheet columns B3:B11, D3:D11 and E3:E11 are currently arbitrary, so you could see what was going on, a mock-up.

FYI, not that it's required for the solution, but look at the Item numbers contained in rows B2:E2, B4:E4 on the Calc sheet and column (A3:A11) to be both part numbers and measurements at the same time.

Again thanks very much for any light you can shed on my problem, as I'm currently in the dark. Your time and energy are very much appreciated.


Later,

Tom

[TommyRoton]

mehmetcik,

Did you give up on me? No worries mate!

I found the solution to the "In Use" column. I entered "=COUNTIF(Calculator.$B$4:$E$4,A3)" into D3 then filled it down to D11. It works great! Now I have to set it up for the "Order" column. At this point though it seems to be quite difficult, but I'll keep hammering away at it and see if I can "SOLVED" my own problem. That shall get me some points.

I was also thinking about a pivot table, but I have no experience with them, so I'm not sure that they'll do what I need.

Again, thanks for your time and effort, it's always greatly appreciated my friend.


Tom

[mehmetcik]

Hi

I have modified your sheet using your formula.

the button reduces your stock available by the new stock required and clears your calculation field.

this allows you to enter new stock required data.

the macro I used is;-

Please Login or Register  to view this content.

[TommyRoton]

mehmetcik,

Did you give up on me? No worries mate!

I found the solution to the "In Use" column. I entered "=COUNTIF(Calculator.$B$4:$E$4,A3)" into D3 then filled it down to D11. It works great! Now I have to set it up for the "Order" column. At this point though it seems to be quite difficult, but I'll keep hammering away at it and see if I can "SOLVED" my own problem. That shall get me some points.

I was also thinking about a pivot table, but I have no experience with them, so I'm not sure that they'll do what I need.

Again, thanks for your time and effort, it's always greatly appreciated my friend.


Tom

mehmetcik,

I solved the final problem of the order column. I entered "=IF(C3<=0,ABS(C3),0)" into E3 and filled it down to E11. Then I deleted row 6 on the calculator page because with my solution it became redundant.


Again, thanks my friend for your time and effort, it's always appreciated!

I'm sure that you've guessed that this spreadsheet has nothing to do with customers. Since in your initial post you assumed it did, I changed it as if it did to suit your assumption, to make you more comfortable, because ones assumption tends to indicate their comfort zone. Of course this just confused you, because you couldn't see how it fit, your minds model of what it should be. Not that it really had to, based upon my actual problems. That's to be expected! All English speakers speak a slightly different language, though it's mostly English. I noticed that years ago; I'm getting old my friend!


Thanks again for your time and effort, as time is a very precious commodity indeed. It's the one thing that in the end, most everyone shall probably want more of.


Tom

[TommyRoton]

Hi

I have modified your sheet using your formula.

the button reduces your stock available by the new stock required and clears your calculation field.

this allows you to enter new stock required data.

the macro I used is;-

Please Login or Register  to view this content.

Oh, I missed this, because I was composing my last message while you posted this one. That's usually par for my course, because I'm slow at both typing and composition.

Thanks for this, I shall look at it and consider it's merits for my particular needs, I'll get back to you after doing so.

Once again, I do appreciate your time and effort, my friend!

One question though, what's file type "XLSM"? Remember, I only have Excel 2003, and I'm not sure it will understand that files formatting..


Tom

[TommyRoton]

mehmetcik,


I confirmed that windows doesn't know what file type .xlsm is, which means that neither does Excel 2003. I do appreciate your work as far as the time and effort you spent though, so thanks again. If you care to save/post it again as an .xls fine, if not I totally understand the problem with tedium.


Tom