Can a cell be created to have a running total that the number never gets smaller?
CELL A input 10
CELL A2 input =100-A1 A2 CELL WOULD =90
I change cell A1 input to 5 but want cell A2 to still output 90. Then change cell A1 to 6 and have cell A2 to output 91. I only want cell A2 to be able to increase. Is this possible? I am new to this so any help is appreciated.
Use an auxiliary cell (or several) where you put state information. But I think you can’t do it only with formulae. You probably need some macro triggered by your entry to store the new state information. Macro is needed otherwise you get a circular set of formulae.
Unfortunately, macro programming is beyond my skills.
product A inventory Qty 10
product A takes 3 parts to make
part 1 Qty in stock 100
part 2 Qty in stock 100
part 3 Qty in stock 100
When assembling product A I want the stock Qty of part 1, part 2, and part 3 to decrease as each product A is entered into product A inventory. I don’t want part 1, part 2, and part 3 to increase as I take product A out of inventory when product A is sold.
As I understand you, the request is about a materials management system. May it even be small size, you need to calculate (update) your current stocks of raw materials and prefabs taken in, groups you integrate yourself, and end products based on transactions like stocktaking, buying, retrieval for integration, selling (…?).
This can’t reasonably be done by spreadsheets, imo. …And there are related tasks for which you would want something like an interface.
Here is a pair of User Defined Functions that I use at times while developing (overly) long cell formulas. They work a little like the Current() function so I can develop cases that otherwise require repeating long formula sub-components without repeating them until I have things working.
It is wrong and twisted to use these for the OQ intent, it is slow, and who knows what could happen. But, it…technically…works.
Here are the UDF’s:
Function Peek(Optional Address As Variant, Optional Pop As Boolean)
Dim NamedRanges as Object
Dim Content As Variant
NamedRanges = ThisComponent.NamedRanges
If IsMissing(Address) Then
Address = "__X__"
Else
Address = "__" & Address 'Use a mini namespace
End If
If NamedRanges.hasByName(Address) Then
Content = NamedRanges.getByName(Address).Content
If VarType(Content) = 8 Then
If Left(Content, 1) = """" and Right(Content, 1) = """" Then
Content = Mid(Content, 2, Len(Content) - 2) 'Remove protective quotes
End If
Else
'NOP
End If
Peek = Content
If Not IsMissing(Pop) Then
'If Pop Then NamedRanges.removeByName(Address)
End If
End If
End Function
Function Poke(Content As Variant, Optional Address As Variant)
Dim NamedRanges As Object
Dim CellPos As New com.sun.star.table.CellAddress
NamedRanges = ThisComponent.NamedRanges
If IsMissing(Address) Then
Address = "__X__"
Else
Address = "__" & Address 'Use a mini namespace
End If
Poke = Content 'Chain back as is before prep for UNO storage
Content = """" & Content & """" 'Protect from UNO converting to lower case
If NamedRanges.hasByName(Address) Then
NamedRanges.getByName(Address).setContent(Content)
Else
NamedRanges.addNewByName(Address, Content, CellPos, 0)
End If
End Function
For the use of these we have:
Please, understand that this is purely academic!
(BTW the Pop option doesn’t work in Peek.)
Hi Bashcan,
in my eyes a verry simple solution: Add this formula in Cell A2:
=IF(A1=“R”,0,IF(100-A1<=A2,A2,100-A1))
I have added a query for “R” at the beginning of the formula so that you can perform a reset. If you enter “R” in A1, it is reset to 0. With every number entered in A1, the result in A2 only goes up.
Since the formula refers to data in the original cell, iteration calculation must be switched on for it to work. Tools - Options - LibreOffice Calc - Calculate - Iterative References - Iterations - enabled.
Add but never substract.ods (11,0 KB)
With kind regards
JĂĽrgen
