Invoice automated using codes, help with formulas

Discuss the spreadsheet application

Invoice automated using codes, help with formulas

Postby aiyukpa » Fri May 18, 2018 12:11 am

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 14 times
OpenOffice 4.1.5
Windows 10 Pro
aiyukpa
 
Posts: 5
Joined: Thu May 17, 2018 11:17 pm

Re: invoice automated using codes, help with formulas

Postby RusselB » Fri May 18, 2018 5:59 am

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4326
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Invoice automated using codes, help with formulas

Postby aiyukpa » Fri May 18, 2018 3:20 pm

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 9 times
OpenOffice 4.1.5
Windows 10 Pro
aiyukpa
 
Posts: 5
Joined: Thu May 17, 2018 11:17 pm

Re: Invoice automated using codes, help with formulas

Postby FJCC » Fri May 18, 2018 4:30 pm

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 6 times
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6463
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Invoice automated using codes, help with formulas

Postby jrkrideau » Fri May 18, 2018 4:45 pm

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.
OpenOffice 4.1.4 Ubuntu 16.04 Xenial Xerus
jrkrideau
Volunteer
 
Posts: 3485
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Invoice automated using codes, help with formulas

Postby Villeroy » Fri May 18, 2018 4:53 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25271
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Invoice automated using codes, help with formulas

Postby aiyukpa » Sat May 19, 2018 3:10 pm

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

Re: Invoice automated using codes, help with formulas

Postby Villeroy » Sat May 19, 2018 4:47 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25271
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Invoice automated using codes, help with formulas

Postby RusselB » Sat May 19, 2018 10:17 pm

If you are finding the Excel tutorials helpful, those same tutorials would help in Calc, especially the very basic ones.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4326
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Invoice automated using codes, help with formulas

Postby aiyukpa » Sat May 19, 2018 11:19 pm

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

Re: Invoice automated using codes, help with formulas

Postby RusselB » Sun May 20, 2018 12:23 am

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4326
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Invoice automated using codes, help with formulas

Postby aiyukpa » Sun May 20, 2018 12:59 am

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

Re: Invoice automated using codes, help with formulas

Postby robleyd » Sun May 20, 2018 3:00 am

Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1595
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: No registered users and 31 guests