FIFO system creation

English
#1

Hi,
I have been trying for hours and hours to solve this with the help of AI but I am giving up now. I need real intelligence, not artificial one, please.

I want to build a simple first in first out system for a storeroom. I have one sheet with the material coming in and one sheet with the stuff being taken out.
The list of items is sorted in rows. In columns then come three entries that repeat for every in and out transaction: date of in/out, number of boxes, expiry date of boxes. So, I have these three column repeating over and over again.
Then I have a third sheet with the summary of everything. In this, I have 5 groups of 2 columns which should show: number of boxes, expiry date.
The idea is now to have 2 formulae in these 5x2 cells that

  1. Checks the corresponding row in the in and out sheets and calculates how many boxes of the same expiry date are still in stock
  2. shows the expiry date corresponding to these boxes.

So, I would like 5x2 dynamic columns in which the quantity of goods with the same expiration date per row is provided. Columns P to Y in the “data” sheet are 5 sets of expiration date and quantity columns. I would like P to show the earliest expiration date and Q the quantity of items with this date. The next earliest is displayed in R and S, and so on. The formula should deduct the check-out from the check-in so that it is always clear what is still in stock and when it will expire, while at the same time keeping the record of old ins and outs that have already been used in the respective in and out sheets. One problem is that normally there is one larger in, and many smaller outs over time.
Here is an example sheet to help those who want to give it a try and proof that their job is not replaceable by AI:
Consumable Stock 2025 - formula development.ods (76.4 KB)

Thanks heaps.

#2

OMG! Are you creating this for a real person or a computer? When I saw the formatted columns EA and EJ, I realized I didn’t want to try to solve a problem that was obviously useless. No, a solution is certainly possible—for example, a well-designed macro can quickly generate the summary table you want. But I know that after a week or two of using this spreadsheet, the user will give up and go back to keeping records on paper.
I suggest that you first think through the table layout and only then look for the necessary formulas.

#3

I am open for better suggestions. At the moment there are absolutely no records. I don’t think that adding 3 values (2 dates and a number) is such a tough call. But maybe I am more pain free than others. Ideally there would be a small dialogue popping up, or you could use a bar code reader. But this is not feasible in this context. I have thought for many days through the table layout, but given who I have to work with, this is the best I could come up with.

#4

A simple flat list of 4Columns with:

| when | what | expiration_date | how_many_units_in or -out |

would do half of the job,
the rest probably by a proper setup of a Pivot-table

#5

You can not expect people to provide the information on the product, THAT is already too much. That is the context I am talking about. Names would be written wrong, or incomplete, or the brand instead of the product, Lot numbers instead of reference numbers. So the what has to be already provided. Given that, I have exactly the info you suggest. I would be happy to organize it differently, but see no way how to do that.

#6

Exactly, you point it, its already to much to consolidate this hairball and pin out the needed information.

#7

Early spreadsheet applications were never intended to be used like this. A spreadsheet is not a database. Any software dealing with inventories and accounting is based on some kind of database. No professional software stores data on spreadsheets.
Simple Inventory Database

1 Like
#8

I really did not come here to get opinions from people who know nothing about the context on how to do the things I do. If you can’t help, maybe just move on. I would be happy about anyone who has a useful contribution to make.

#9

So you have to ask @Tass

If you read a bit of the contributions of all 3 who replied, you could know: They can help. If they ask questions instead of doing this I recommend to start thinking.
.
I will move on, as I would not attempt this in Calc. Maybe a database, maybe some programming language, but not Calc.
I wish you good luck.

#10

although I would agree that prevention against Anti-pattern - Wikipedia and XY problem - Wikipedia matters,
it would not hurt to promote a bit of reflexion about why their same bunch of (legacy) contributors (leaders) are jumping over answering (with the same jaded prism);
isn’t it a bit disuasive for (normal) “users”, newcomers, new contributors, to jump in as well and bring fresh views ?
isn’t it a bit detrimental in maintaining FAQs and documentation ?
…

ps.
of course : About - Ask LibreOffice :expressionless:
and for the motivational mantra : https://community.documentfoundation.org/t/moving-forward-how-we-can-unblock-the-situation-together/13270 :wink:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.