Page 1 of 1

[Solved] Invoice automated using codes, help with formulas

Posted: Fri May 18, 2018 12:11 am
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

Re: invoice automated using codes, help with formulas

Posted: Fri May 18, 2018 5:59 am
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.

Re: Invoice automated using codes, help with formulas

Posted: Fri May 18, 2018 3:20 pm
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?

Re: Invoice automated using codes, help with formulas

Posted: Fri May 18, 2018 4:30 pm
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.

Re: Invoice automated using codes, help with formulas

Posted: Fri May 18, 2018 4:45 pm
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.

Re: Invoice automated using codes, help with formulas

Posted: Fri May 18, 2018 4:53 pm
by Villeroy

Re: Invoice automated using codes, help with formulas

Posted: Sat May 19, 2018 3:10 pm
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.

Re: Invoice automated using codes, help with formulas

Posted: Sat May 19, 2018 4:47 pm
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

Re: Invoice automated using codes, help with formulas

Posted: Sat May 19, 2018 10:17 pm
by RusselB
If you are finding the Excel tutorials helpful, those same tutorials would help in Calc, especially the very basic ones.

Re: Invoice automated using codes, help with formulas

Posted: Sat May 19, 2018 11:19 pm
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.

Re: Invoice automated using codes, help with formulas

Posted: Sun May 20, 2018 12:23 am
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.

Re: Invoice automated using codes, help with formulas

Posted: Sun May 20, 2018 12:59 am
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.

Re: Invoice automated using codes, help with formulas

Posted: Sun May 20, 2018 3:00 am
by robleyd

Re: Invoice automated using codes, help with formulas

Posted: Sun May 27, 2018 3:04 am
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.