Excel Help

[[BTF]adam]

soup,

I have been asked to make a stock control sheet for a family friend, it isnt overly complex but basically I need the following features and my excel is very rusty, so here's what im struggling with atm:


   Stock   Price   Number Required   Total
Paints            
Cadmium Red      £4.88      £0.00
Permanent Magenta      £3.67      £0.00
Alizarin Crimson      £3.25      £0.00
Cadmium Yellow      £4.88      £0.00
Lemon Yellow      £4.50      £0.00
Auriolin      £4.88      £0.00
Indian Yellow      £3.25      £0.00
Cadmium Orange      £4.88      £0.00
Green Gold      £3.57      £0.00
French Ultramarine      £3.57      £0.00
Cobalt Blue      £4.88      £0.00
Cerulean Blue      £3.67      £0.00
Quinacridone Gold      £3.67      £0.00
Olive Green      £3.25      £0.00
Burnt Umber      £3.25      £0.00
Raw Umber      £3.25      £0.00
Raw Sienna      £3.25      £0.00
Burnt Sienna      £3.25      £0.00

that is going to be formatted terribly im sure, but I hope you can see what I mean at least with the columns and rows.

what I mainly need atm is a way to, when the stock is filled in (say with 6 in each stock cell), and someone comes to my dude and buys say, 5 tubes of cerulean blue. well I've got the functions for totalling it up and stuff, but when my dude puts 5 in the Number Required box, I need the following to happen:

a) takes away whatever was just put into the number required box from the stock box. I think that is fairly simple, =SUM(norequiredcell)-(stock) if I am correct?

but then b) throws up a message box when say, the stock goes under 2 to remind him to restock this paint.

actually I just tried that (a) and it comes up with an error, I suppose it cannot calculate it like I want it to every time he puts a number in the "no wanted" box. I could make a stock and stock remaining box? but then that sort of defeats the purpose i guess? bleh i dunno

[[BTF]adam]

after googlin a bit I came up with this off a yahoo answer thing


Private Sub Worksheet_Calculate()
If Range("A1") = "Available" Then MsgBox "Cell A1 indicates Available"
End Sub


could I modify that vb macro perhaps? maybe something along the lines of



Private Sub Worksheet_Calculate()
If Stock Left("E1") = "<2" Then MsgBox "This paint has dropped below 2 in stock, you need to restock the shelf!"
End Sub

[Whirlingdervish]

your best bet is making vb macros to do that.
you would want to do your comparisons correctly tho:


If Stock Left("E1") = "<2" Then

!=

If Stock Left(E1) < 2 Then



you wouldn't want to compare an integer or decimal value to a string or you'll get all sorts of funky shit going on.

[[BTF]adam]

fuck I hate VBA

Allow me to lay out exactly what I require because after a while fiddling I still can't get this to work, I am not even sure how to implement it.

On the cells F3-F20 (and more but for the purposes of right now), when the number in those cells drops below 2, I need a simple MsgBox to pop up with "Restock this paint".

In cells F3-F20 there is a SUM which minuses the "Number Required" cell with the "Stock" cell to let the user know the "Stock Remaining (F3-F20)"

Now, I  presume I need a Worksheet_Change thing.. or something. Everything I have come up with gives me a syntax or compile error.

Best I have come up with is something like

Sub Worksheet_Change()
If F3 - F20 = < 2 Then MsgBox "Restock This Paint"
End Sub

when I run this it runs without a problem, it is saved as a macro called "restock" in my workbook but nothing happens if I change the corresponding cells that makes the stock remaining cell go to 1 left.

[reaper]

Usually I like to do shit in unixy land, but it's funny when someone grabs excel, makes a couple mouse moves, and magically they got what they want.

Sorry don't know Excel.  Why is the value of cell remaining moving over to the left.  Sholdn't it put columns, 1,2,3,4,5.  Maybe if it has to move you can reference a cell by the distance from the other cell.

[[BTF]adam]

in the end what seemed so simple ended up not so simple had to ask an excel help board and what worked eventually was the following:

Code: [Select]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell        As Range
    Dim rInt        As Range
    Dim rOut        As Range

    Set rInt = Intersect(Target, Range("B3:B24"))
    If rInt Is Nothing Then Exit Sub

    For Each cell In rInt.Cells
        If cell.Value < 2 Then
            If rOut Is Nothing Then Set rOut = cell
            Set rOut = Union(rOut, cell)
        End If
    Next cell

    If Not rOut Is Nothing Then
        rOut.Select
        MsgBox "Restock selected item(s)"
    End If
End Sub


[peewee_RotA]

I think that I'm pretty savvy when it comes to Microsoft Word Documents. However, when it comes to spreadsheets, I have never really excelled.

[yahoo]

I think that I'm pretty savvy when it comes to Microsoft Word Documents. However, when it comes to spreadsheets, I have never really excelled.

same here. lol i even just learned recently how to link data from different sources. BTW im good in powerpoints coz i used to make porn pic sideshows way back on dialup years and video streaming was never heard of .