Apache OpenOffice Community Forum

So, I want to create a column of numbers from two other columns of numbers, as part of an invoice writing document. Column A is a series of multiples(amounts of products), some of which are 0. Column B is an index of column A(product code numbers). I want Column C to be populated by the product code numbers corresponding to the cells in A that are not zero, without duplication.

I have already set up a function that finds the correct cells for prices, product name, etc for each product code number in C, but I have to enter them manually right now and I'd like it to be automated from column A and B.

I've exhausted my meagre knowledge of Calc already. I can make a function that finds the numbers >0 in A and prints the correct B, but then it prints only the top one for all rows. I haven't found a way to exclude already referenced cells from the search. Does anyone have any advice?

Stop trying to force a spreadsheet to do something beyond its design parameters, your problem needs a database. For example
[Example] Invoice Forms (without macros)

@kups93: Welcome to the forum with your first post.
@UnklDonald418: Isn't your answer a bit doctinair?

The advice to move to a database with any task roughly concerning business is often found in this forum. It's true. Databases come with lots of features and tools, they offer fundamental help with data integrity and many relevant requirements. Business tends to grow. (Hopefully?) Someone else will have to handle the software tool without understanding it, and thus in danger to spoil things. ...

However, I would consider if not everything depends on a scale somehow.

If I sell two dozen different articles and am sure there will never be more than three dozen. If I, in addition, have two dozen customers and am sure the number will rather decrease than increase. If I know everything in my little realm personally and am sure this will not change. If I am sure to be able to organise the backuping as needed...

...I will not do the hack with a database, but with a few spreadsheets.

Why? Are you really interested?

@kups93 again:
I would recommend not to treat the "code numbers" as numbers, but as texts, and therefore they should not have a format tempting Calc to "recognise" them as numbers. (Actual numbers are for calculating, not for identifying.)
For a few ways to get waht you seem to need, see the attachment.

If you do it on sheets, you have to know everything about spreadsheets, not counting all the built-in functions but counting all the aspects related to data types, absolute/relative referencing, expanding/shrinking sheet cell ranges, formatting vs. values, all the data related features (filter, sort, pivots etc), all the things that may go wrong when handling such a free-hand tool and how to signal errors and how to undo them.

With some basic knowledge about databases you can get much more than you will ever get from a sheet. However, the basic knowledge about databases is more abstract, more difficult, thus more powerful.

This is why everybody uses a ready made software product. If there were any viable spreadsheet solution, it would be a popular one. I've only seen one single working spreadsheet example dealing with invoices. It was an example template for Excel '97 made by Microsoft. It was full of sophisticated VBA code and pulled data from a database connection. I used it for a few months but I had to modify the Access database and found out that a database report yields better looking invoices than the Excel template.