Excel - stock sheet

  Craig.m 08:50 23 May 2007
Locked

I have been asked if it is possible to have Excel keep track of stock. The way they want to do it is not going into a stock level and just adding or subtracting, they want to put a value in for items added or remove then this will be added or removed from the stock value held elsewhere on the sheet.

What would be the best way to achieve this, have a front page where the items removed added could be put into the relevant column, then have a macro take those values and add or remove them from the stock level and zero once done?

I can see that this may have drawbacks in that someone may not be sure they have done it if the numbers go once they have been used for the update, unless the macro or whatever also changes a cell to give last updated date.

I think the easiest input method would be for a drop down to select the item that was being added or removed to make the entry sheet a little easier.

Would anyone have any views on this and is it easily achievable?

Craig

  wee eddie 09:24 23 May 2007

Unless this is watertight, it really doesn't matter what you do, the exercises will only be a face saver.

Personal experience speaking here.

My system was based on a Database rather than a Spreadsheet, but that's neither here nor there.

As only about three quarters of the Staff actually recorded their Requisitions and the Delivery Invoices were frequently a week behind. The figure meant little. Good Luck

  VoG II 10:29 23 May 2007

This is definitely feasible as I have seen such systems in operation. However, if there were more that a handful of different stock items it would become unwieldy.

I would simply have a sheet for each stock item with 3 columns:

A = Date

B = Input/Output quantity (negative for goods out)

C = running total if the initial stock level is in C2 and the first input/output then in C3 enter =C2+B3 and copy the formula down.

You could then have a 'dashboard' sheet showing the current stock levels for each item. To find the last value in column C you could use

=INDEX(C:C,MATCH(0,C:C,-1))

  VoG II 10:30 23 May 2007

Sorry for the typos - hopefully it is understandable!

  Craig.m 12:20 23 May 2007

I am not sure how many items there will be but not too many, it is a small forward stock room that engineers dip in and out of.

The only issue I see is that having a stock cell and then next to it the additions or subtractions is that the stock will only be valid for the first transaction, as soon as the next transaction number is put in it will change the value in C but only by the number in there.

I was thinking that a calculation could be done using the input to a cell and the number (stock level) permanently updated by that amount.

There is a secretary who the engineers tell what they have taken/put in and she wants to put a number next to the relevant item and hit enter (or a fuunction button) and the totals will be updated for her.

I can put some different bits on that will warn herr when things go below a certain level but it is this taking a number and adding to a total. The only way I can think of is to record a macro that will take the in/out number add it to the stock number then overwrite the prvious stock number with the resultant and ziero the input field ready to take another number next time. Does this make sense? I am trying to make it very simple for the secretary to do - put in a number and everything is updated for her

  VoG II 12:47 23 May 2007

You could probably use an event-driven macro to do this; as a simple example.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "A1" Then
Sheets("ITEM1").Range("A1").Value = Sheets("ITEM1").Range("A1").Value - Target.Value
End If
End Sub

To use this, right click a sheet tab > View code and paste the above in. Make sure that you have a sheet called ITEM1 then enter a value in A1 of the sheet that you added the code to and observe the value in A1 in ITEM1.

To make this useful, you would need a Select Case statement to identify the possible cells where you might be entering values and where the results should go.

  VoG II 13:23 23 May 2007

Actually, there is an easier way with everything on one sheet. For example with the stock levels in Row 5 and additions/subtractions entered in Row 3:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Row <> 3 Then Exit Sub
Application.EnableEvents = False
Target.Offset(2, 0).Value = Target.Offset(2, 0).Value - Target.Value
Application.EnableEvents = True
End Sub

  Craig.m 21:42 23 May 2007

Thanks for that VoG, I will try and have a fiddle and let you know how I got on.

Craig

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires