Excel Formuale

Hi there, might be a stupid question but need help.
Got a spreadsheet set up for stock.  Now i need to be able to update the stock levels on 2 occasions
1 When stock is used
2 When stock recived

I need to know if there is anyway i can set it to update when the stock is recived or used in relation to the current stock.  I have created a formuale that can update it but then when other stock is reived or taken it is not taken into account, so i need a formulae that can be changed as the book changes to reflect the stock status.  I think i explaining this wrong.  
I have a current stock level that will decrease over time as it does of 3/4.  I have a used level of 1/4 leaving a level of 1/4.  I now need to re order 1/4 to give me a half.  How can i just update the re order level to make the current stock show a half with out changing anything else other than delteing the used level?
Please say if not understand
LVL 7
dann47Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ACSIPaulCommented:
Hi,

I'm sorry, but I do not understand.
dann47Author Commented:
Did not think so.
I have a figure that is worked out by a formulae.  When that figure changes in the formule, i want it to stay there when i say delete the number that is part of the formula.
Basically i am doing a stock sheet, that needs to keep the level of stock constant, and the 2 variables are stock used and re ordered.  When i take out of stock used, i want it to decrease the stock, then delte the value out of the stock used for next time.  When i add into reorder, i want the stock level to be incresed and kept there when i delete the value.
god this is confusing

Cheers tho
CarlPritchardCommented:
I doubt this is possible using formulae only, it probably requires some programming.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

dann47Author Commented:
Either or, i managed to create a macro but this is a slow way of running it
CarlPritchardCommented:
This is a pointer that may help if you know a little VBA:

Place this code in the VBA editor under Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    'Test that it is the cell you want to respond to
    If Target = Range("A5") Then
           'Check that the value is not 0 (as changing the value to 0 in this code re-triggers the event. This is VV important as        you could get  recurrsive loop using this event.
           If Range("A5") = 0 Then Exit Sub
           'Change the values.
           Cells(10, 2) = cells(5,1).value+cells(10,2).value
           Cells(5, 1) = 0
    End If
End Sub

In this example, entering a value in A5 triggers the event, the value is added to B2, A5 is then reset to 0. The line with Exit Sub is very important as when the code changes the value to 0 the event is fired again.

Hope this provides a leader.
dann47Author Commented:
No idean on that mate. any chance you could recompile please.  The cell values are as below

d9:d33  Where i want the total stock
e9:e33  Where there is the used amount
f9:f39    The re order quantity

So the last 2 reset to 0 and the first with the kept total

Cheers in advance
CarlPritchardCommented:
This will probably do what you want:


Private Sub Worksheet_Change(ByVal Target As Range)
   
    'Test that it is the cell you want to respond to
    Dim intRow As Integer
    Dim intCol As Integer
    Dim intValue As Integer
    Dim cell As Range
   
    For Each cell In Target.cells
        intRow = Target.Row
        intCol = Target.Column
        intValue = cells(intRow, intCol).Value  
        'Used - reduce stock
        If (intRow >= 9) And (intRow <= 33) And (intCol = 5) And intValue <> 0 Then
            cells(intRow, 5) = 0
            cells(intRow, 4).Value = cells(intRow, 4).Value - intValue
        End If
        'Restock, add to stock
         If (intRow >= 9) And (intRow <= 33) And (intCol = 6) And intValue <> 0 Then
            cells(intRow, 6) = 0
            cells(intRow, 4).Value = cells(intRow, 4).Value + intValue
        End If
       
    Next
   
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dann47Author Commented:
bINGO Cheers mate
dann47Author Commented:
Hello again, anychance of this for free? need a code that can change a + to a > and i - to a < in a cell

Anyideas pleas?
CarlPritchardCommented:
Need more explanation to a) see if it easy enough for number of points offered already b) to actually be able to do it.

Note moderator may prevent a new question on an existing closed thread.
dann47Author Commented:
Need a quick VB script for excel.  Here the problem

In a coloumb say a1:a20 i need to be able to make the character change as follows

if type + need to change to >
If type - need to be <
if nothing in leave as blank

Much appreciated i already allocated the points to you mate
CarlPritchardCommented:
Cannot (to my knowledge) change what is typed on the fly like that. It can be changed once they hit return using a variation of the technique I gave before - but only once they have hit return to enter the data.

Are they entering a formula or some text? It would be useful to see the typical input and what the cells are actually doing.
dann47Author Commented:
Enter is ok, basically it show more than or less than.  i want them to type plus or minus so it is then changed to the relevent symbol.  

It is text in this certain cell

Thanks for your help, your a good lad
CarlPritchardCommented:
I am assuming this is in the same Sheet:

Add this with the other DIM statements in the existing code:

 Dim strSignTest As String


Add this between End If and Next in the current code:

        strSignTest = Cells(intRow, intCol).Value
        If (intRow >= 1) And (intRow <= 10) And (intCol = 1) Then
            If strSignTest = "+" Then Cells(intRow, intCol) = ">"
            If strSignTest = "-" Then Cells(intRow, intCol) = "<"
        End If
dann47Author Commented:
I afraid it not mate in a sheet next to it
CarlPritchardCommented:
Insert this into the appropriate sheet in the


Private Sub Worksheet_Change(ByVal Target As Range)
   
    'Test that it is the cell you want to respond to
    Dim intRow As Integer
    Dim intCol As Integer
     Dim strSignTest As String
    Dim cell As Range
   
    For Each cell In Target.cells
        intRow = Target.Row
        intCol = Target.Column
         strSignTest = Cells(intRow, intCol).Value
        If (intRow >= 1) And (intRow <= 10) And (intCol = 1) Then
            If strSignTest = "+" Then Cells(intRow, intCol) = ">"
            If strSignTest = "-" Then Cells(intRow, intCol) = "<"
        End If    Next
   
End Sub
dann47Author Commented:
Sorry for the delay in response.  Get a compile error on
End If    Next

Syntax error

Any ideas?
CarlPritchardCommented:
Sorry, my fault, Next needs to go onto next line.
dann47Author Commented:
Cheers i thought so, i tried it but it does not seem to work.  The code is ok just does not do anything in the cells.  I changed test to D10
Nothing happens in D10
CarlPritchardCommented:
It won't work on D10, it is programmed for A1 to A10.

You would have to change
If (intRow >= 1) And (intRow <= 10) And (intCol = 1) Then

to change the row/column checked, intCol=1 refers to column A. To test on D10 you would have to change (intCol=1) to (intCol=4).
dann47Author Commented:
Aghhh must say this is helpin a lot, i going to study this i think quite intresting.

And bingo it works.  You are a superstar.  One last thing and recommendations on a good book for this?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.