[Solved] Invoice automated using codes, help with formulas

Discuss the spreadsheet application
Post Reply
aiyukpa
Posts: 6
Joined: Thu May 17, 2018 11:17 pm

[Solved] Invoice automated using codes, help with formulas

Post by aiyukpa »

I am new to OpenOffice, hope I am not duplicating a topic. Couldn't find one similar.

I am trying to create a workbook using one sheet for inventory (SKU, Items, Cost per Item) and a second sheet for an invoice that will autofill product descriptions when entering the SKU. I was able to create this in Excel but having no success whatsoever in Calc! Cannot even get the invoice to link to the items sheet much less the formulas to make the invoice work! Here's the extra issue...

An extra step is needed for items that are priced by length. Example: 8"-wide metal roofing is $ 1.00 per inch. I was willing to write it up as total inches but customers like to see the Feet/Inches so they know they're getting 4 lengths of metal that is 8' 3" each. The invoice should appear:

(see attachment)

I cannot get anything to work in Calc but really like its options and the idea of using OpenOffice instead of Windows. If anyone can offer help it would be GREATLY APPRECIATED.

Where to begin, where to find an easy-to-understand how-to for formulas. I have searched our library system and unable to find anything about Calc formulas. THANK YOU
Attachments
sample invoice.docx
(11.31 KiB) Downloaded 120 times
Last edited by aiyukpa on Sun May 27, 2018 3:10 am, edited 1 time in total.
OpenOffice 4.1.5
Windows 10 Pro
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: invoice automated using codes, help with formulas

Post by RusselB »

My first suggestion, based on your statement that you were able to create what you are wanting in Excel, is to open the Excel file in Calc (the spreadsheet portion of OpenOffice)
While the Excel file may not open exactly correct, the amount of work required to fix what doesn't work would be a lot less than trying to recreate the entire thing in Calc.
You might also try the Calc portion of LibreOffice, as LibreOffice has been reported to have a higher compatibility level with Excel.
In either case, the file you attached is of the wrong format for usage in a spreadsheet. Excel files are usually .xls or .xlsx extensions, and Open/LibreOffice Calc files usually use the .ods extension.
The .docx extension in the file you uploaded tells me that it is a Word document, which might open in Writer, but is not a spreadsheet document.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
aiyukpa
Posts: 6
Joined: Thu May 17, 2018 11:17 pm

Re: Invoice automated using codes, help with formulas

Post by aiyukpa »

Thank you for the reply. The docx was to show only the appearance of the invoice. Attached is the .ods file.
At this point, I am giving up. I am now trying to find a legitimate business person to create the formulas for me as I have spent more than 12 hours on this one project! I have done a Google search for spreadsheet experts, called the local high school and a vocational school asking for teachers, called the CPA to see if he could refer me to someone who creates spreadsheets. No results!
I need to be able to add probably up to 40,000 line items. I get a few to work but when I add more items, it stops working. And I can do nothing with the Feet and Inches columns.
Can anyone suggest a legitimate business, probably in the U.S., who will put in the formulas?
Attachments
invoice template v9.ods
(16.1 KiB) Downloaded 113 times
OpenOffice 4.1.5
Windows 10 Pro
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Invoice automated using codes, help with formulas

Post by FJCC »

Is the attached file close to what you are looking for? The formulas in column G assume that all items that are priced by length are priced by the inch.
Attachments
Invoice.ods
(17.15 KiB) Downloaded 133 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Invoice automated using codes, help with formulas

Post by jrkrideau »

My first though when I saw add probably up to 40,000 line items was that you need to forget using a spreadsheet.

If you are running a business with that many items in stock and presumably a large number of customers you really need a data base with a properly designed invoice form. A data base that hopefully will maintain up-to-date customer information as well.

The problem with going for the data base solution is that data bases for a beginner are a lot harder to create and you probably will need to get professional help though the vocational school (Community college in my terms?) seems like a good idea.

The advantage of going with the data base solution is that you get much better data security, you can build in data checking into the data entry process, and it is a lot harder for a user to mess up the data when using it. A spreadsheet, in this kind of use, is a disaster waiting to happen.

I don't understand your comment about the Feet and Inches columns.

We have a Paid Support forum (scroll way down on the main screen) but I have no idea how well it works.
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Invoice automated using codes, help with formulas

Post by Villeroy »

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
aiyukpa
Posts: 6
Joined: Thu May 17, 2018 11:17 pm

Re: Invoice automated using codes, help with formulas

Post by aiyukpa »

FJCC
Yes, exactly! Thank you so much! Yes, the feet-inch is priced by the inch and that is spot-on. But again, the issue with new items added to the ITEMS sheet of the workbook not being recognized by the formula on the invoice.

Obviously, I have no experience with formulas/spreadsheets. What I was able to accomplish in Excel was only because I found YouTube video tutorials and copied exactly what was done. When something needs to be tweaked to fit my scenario, I do not have the knowledge to do it. Example, near the bottom of the invoice where the subtotal, shipping, tax, total, are, I have no clue how to do all that in Calc, although there is probably a tutorial online for Excel.

Giving up. Back to trying to find a local expert to do the formulas for me. Thank you, all, for the suggestions.

jrkrideau,
Thank you, also, for your answer. Probably a data base is a good idea. However, the family who owns this small business wants to continue using OpenOffice Calc so that is what I am trying to do. They said they would be happy with just the items they sell most often rather than the entire inventory.

Villeroy
Thank you for your reply but I am afraid it was all ‘over-my-head’ so, although I’m sure the explanation was clear to some who have a little knowledge of these things, I am not one of those people.
OpenOffice 4.1.5
Windows 10 Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Invoice automated using codes, help with formulas

Post by Villeroy »

You can not handle spreadsheets (inadequate tool anyway).
You can not handle a ready made database solution.
So you can not have any invoicing system other than hand written invoices.
Simple as that.

Some templates for hand written invoices: https://templates.openoffice.org/en/sea ... _order=ASC
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
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Invoice automated using codes, help with formulas

Post by RusselB »

If you are finding the Excel tutorials helpful, those same tutorials would help in Calc, especially the very basic ones.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
aiyukpa
Posts: 6
Joined: Thu May 17, 2018 11:17 pm

Re: Invoice automated using codes, help with formulas

Post by aiyukpa »

Well, I said I was giving up but - being stubborn and hating to fail - I have kept trying because something like this will be perfect for a friend of mine who sells homemade soap, only a few types...
Thanks again to FJCC for making the feet and inches columns work perfectly.
So far, I have figured out how to add extra line items to the ITEMS sheet then add those to the formula on the QUOTE (Invoice) sheet.
Now, trying to figure out ...
1) how to hide the #N/A on blank lines of the invoice the customer does not see the #N/A when saved as PDF or when printed
2) how to finish by writing formulas for the subtotal, a discount (if desired), add shipping and sales tax, then a grand total
Still searching online tutorials. If I find ones that are truly helpful and applicable to this subject, I will post a link.
OpenOffice 4.1.5
Windows 10 Pro
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Invoice automated using codes, help with formulas

Post by RusselB »

For hiding the #N/A, check the ISNA function
Sub-totals can be as simple as using the SUM function
Discounts can be trickier as some businesses offer bigger discounts for larger quantities (eg: under 10 - no discount, 10-24 gets 5%, 25-49 gets 10%, etc.) or it could be a flat dollar discount, or a combination of both.
Shipping & handling will depend on how it is being shipped and to where.
Sales tax will be a percentage, but of the sub-total before discounts or after depends on the laws in the appropriate area.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
aiyukpa
Posts: 6
Joined: Thu May 17, 2018 11:17 pm

Re: Invoice automated using codes, help with formulas

Post by aiyukpa »

Thank you EVERYONE for helping. I think I did it! After testing it some more to be sure, I'll let you know.
OpenOffice 4.1.5
Windows 10 Pro
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Invoice automated using codes, help with formulas

Post by robleyd »

Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
aiyukpa
Posts: 6
Joined: Thu May 17, 2018 11:17 pm

Re: Invoice automated using codes, help with formulas

Post by aiyukpa »

FJCC - Thanks to your help with the formula for items measured by feet and inches, priced by the inch, I was able to figure out the formula for pricing feet and inches when the line item is priced by the foot.

=IF(ISBLANK(B20);"";IF(AND(ISBLANK(D20);ISBLANK(E20));F20*A20;(D20*F20)+(E20*(F20/12)))*A20)

Hope that may help someone else.

I have really enjoyed figuring out how to use Calc even though it was frustrating at first.
OpenOffice 4.1.5
Windows 10 Pro
Post Reply