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?
Invoice automation advice?
Invoice automation advice?
OpenOffice 4.1.6 on OS X 10.10.5
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Invoice automation 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)
[Example] Invoice Forms (without macros)
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Invoice automation advice?
@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.
@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.
- Attachments
-
- aoo96551someWaysOf_1.ods
- (91.61 KiB) Downloaded 89 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Invoice automation advice?
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice