# 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?
LVL 7
###### 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.

Commented:
Hi,

I'm sorry, but I do not understand.
Author 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
Commented:
I doubt this is possible using formulae only, it probably requires some programming.
Author Commented:
Either or, i managed to create a macro but this is a slow way of running it
Commented:
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.

Author 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

Commented:
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
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

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

Author Commented:
bINGO Cheers mate
Author 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?
Commented:
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.
Author 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
Commented:
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.
Author 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

Commented:
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
Author Commented:
I afraid it not mate in a sheet next to it
Commented:
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
Author Commented:
Sorry for the delay in response.  Get a compile error on
End If    Next

Syntax error

Any ideas?
Commented:
Sorry, my fault, Next needs to go onto next line.
Author 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
Commented:
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).
Author 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.