Formula help for invoice to statement

Discuss the spreadsheet application

Formula help for invoice to statement

Postby erikashae808 » Fri May 03, 2019 8:17 pm

Im using a template that is made for statements however, i would like to add an invoice register page to the spreadsheet tabs and be able to have it autofill the customer invoice information when i put their name in. i've already got it fixed to add their address when i do that from a customer information page. i just cant get the same formula to work how i need it for the invoices. i need it to sort and load multiple(undetermined amount) lines of data. if i need to make 2 sheets and multiple formulas or whatever i can. just need help fine tuning.

Using Account Statement template from SpreadSheet123. I've added a customer tab with the information for their addresses. Used a dropdown menu for the customer name and used an array formula for the address to populate. that works without flaw. if you need more info let me know.
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby MrProgrammer » Fri May 03, 2019 10:21 pm

Hi, and welcome to the forum.

erikashae808 wrote:i would like to add an invoice register page to the spreadsheet tabs and be able to have it autofill the customer invoice information when i put their name in. … i just cant get the same formula to work how i need it for the invoices.
You'll probably want to use VLOOKUP but you didn't attach your speadsheet so we don't know what you need. Attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Tell us your goals.

erikashae808 wrote:i need it to sort and load multiple(undetermined amount) lines of data
That is too vague for anyone to help directly. But study this:
[Tutorial] Sorting and Filtering data with formulas

erikashae808 wrote:I'm using a template that is made for statements however, i would like to add an invoice register page
Instead of programming this yourself, you could consider some of the many accounting software programs that are available. Some are free/open source software. Yes, you will need to spend some time to find one that meets your needs, but you can then avoid spending time on developing your own spreadsheet. The accounting software will provide you far more features than any spreadsheet you could develp. Your time is perhaps better spent on researching accounting software than on Calc study. I will guess that your question relates to the Point of Sale (POS) and perhaps Inventory Management (IM) components of the software.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3782
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Formula help for invoice to statement

Postby RusselB » Sat May 04, 2019 3:27 am

I have to concur with MrProgrammer on all points.
The number of options that may or may not work for what you are trying to do is. almost, limitless.. but without seeing how you have your spreadsheet layed out, we are just guessing.
OpenOffice 4.1.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5294
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula help for invoice to statement

Postby erikashae808 » Tue May 07, 2019 6:38 pm

So the one im using is from spreadsheet123 but i have added a few things. But when I use the VLookup formula in the bottom section, it loads everything vertically and not horizontally. but i need it to be able to load ALL invoices under the customers name lets say up to 15 invoices. And as i am aware it would be "easier" to find a billing software, none of the cheap/free ones that i have found do all the functions that i need, thus is why im trying to create my own. basically im just having a brain fart from my excel training and i can not for the life of me figure out the way to fix that. in my defense its been about 8 years since ive done ANY spreadsheet formulas hahaha. and it wont allow me to post the whole file so im having to break things down a bit
Attachments
tempstatement.ods
(18.25 KiB) Downloaded 19 times
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby RusselB » Tue May 07, 2019 10:09 pm

If the file size is what's keeping you from giving the whole file (though we probably don't need it), you can upload your full file to a filesharing site, like Dropbox or MediaFire, then post a link to the uploaded file on the forum here.
In the meantime, I'll take a look at what you did post.
OpenOffice 4.1.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5294
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula help for invoice to statement

Postby RusselB » Tue May 07, 2019 10:20 pm

Where is the invoice information coming from? Without that, any advice or suggestions would be a waste of time.
OpenOffice 4.1.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5294
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula help for invoice to statement

Postby erikashae808 » Tue May 07, 2019 10:26 pm

I will have to manually input the invoice information because we are still doing hand written invoices. the file i uploaded has a tab for invoices already put in. after i get that formula set then i should be able to finish out what all i need to do.
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby RusselB » Tue May 07, 2019 11:12 pm

ok.. Sorry, for some reason I didn't see the Invoice tab...
Now that I have that to work with, I'll see what I can come up with.
Finding the first invoice in the list is easy, the VLOOKUP function will do, that, but finding the one's after that, or ensuring that you don't get invoices from a previous billing in the current billing does make the task more difficult.
Out of curiosity, I noticed in the Line Total column of the Billing Statement, you use the OFFSET function, yet I can see no reason for it, as the math is just column I minus column K
I'd suggest removing the OFFSET function, as you're just making Calc work a bit harder than it needs to, unless you'd like to explain why you are using it, in which case the reason for using it may show me what the difference is.
OpenOffice 4.1.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5294
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula help for invoice to statement

Postby erikashae808 » Tue May 07, 2019 11:30 pm

RusselB wrote:Out of curiosity, I noticed in the Line Total column of the Billing Statement, you use the OFFSET function, yet I can see no reason for it, as the math is just column I minus column K
I'd suggest removing the OFFSET function, as you're just making Calc work a bit harder than it needs to, unless you'd like to explain why you are using it, in which case the reason for using it may show me what the difference is.


So as for the offset... that was part of what spreadsheet123 added in. just something i havent taken out. its keeping a running ledger total for the customer. which i dont necessarily need. i just havent removed it
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby erikashae808 » Tue May 07, 2019 11:41 pm

Ok so a little more clarification.... if A11 on Billing Statement sheet =AAA i want A18 and across to populate the first invoice info and A19 the next invoice etc. until all said customers invoices are listed.
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby RusselB » Tue May 07, 2019 11:51 pm

So you want all invoices, irrelevant to the billing term?
If so, then you would all of the invoices, once they were entered, showing for all months/billing periods.
This is not a practice that I would want in any business that I was running, and your customers aren't going to want it either, as they will (probably) expect to see just the invoices for the current billing period.
OpenOffice 4.1.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5294
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula help for invoice to statement

Postby erikashae808 » Wed May 08, 2019 12:02 am

We don't really do a billing term other than due on receipt. and basically i will clear them out and start with a clear slate of invoices every month. and save as a different pdf for that month. until now i have been handwriting all invoices and statements monthly. I am slowly trying to switch to all electronic as to not to confuse the other employee and plus i still have like 800 invoices to go thru (which will be a year or so) before i can switch over as to not have wasted our money. What I'm trying to do is build a WorkBook so that I can have sales tax, invoices, and statements all together, still be able to print or save a pdf file of each statement as needed (which not all programs do). We are a very small business and have been old school for 40 years and we are trying to minimize costs as much as possible so asking my dad to pay for Quickbooks is out of the question.... even at 5 or 10 a month.
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby RusselB » Wed May 08, 2019 12:40 am

OK... This gives me a better idea as to what you are looking for in the long run, and I'll take that into account, along with information I have from running my own small business, to give you something that, to the best of my ability, will give you what you want.
OpenOffice 4.1.6 and LibreOffice 6.0.6.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.
User avatar
RusselB
Moderator
 
Posts: 5294
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula help for invoice to statement

Postby Villeroy » Wed May 08, 2019 10:54 am

Please stop this project. You must not use any kind of spreadsheet for this task. Sooner or later you will get unrecognized undetected errors.
Last edited by Villeroy on Wed May 08, 2019 6:14 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26977
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula help for invoice to statement

Postby erikashae808 » Wed May 08, 2019 3:00 pm

Villeroy wrote:Please stop this project. You must not use any kind of spreadsheet for this task. Sooner or later you will get unrecognized errors.


Thanks for the advise, but I am going to continue. If/when I start getting errors then I will adjust my needs. I prefer to not have negativity about things considering I am already stressed from running a 200k a year business alone. Quickbooks and other "financial" programs are all based off of spreadsheets so the mere thought of not using a spreadsheet for this is ignorant in itself.
OpenOffice 4.1 on Windows 10
erikashae808
 
Posts: 7
Joined: Fri May 03, 2019 8:02 pm

Re: Formula help for invoice to statement

Postby RoryOF » Wed May 08, 2019 3:07 pm

Actually, Quickbooks uses an underlying database, with its input and output on custom forms designed to resemble spreadsheets.

With respect, your expertise is running your business, not writing software to manage it. Consider finding a nearly acceptable off-the-shelf product, and adapting your requirements to the facilities it offers.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29274
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Formula help for invoice to statement

Postby keme » Wed May 08, 2019 3:42 pm

erikashae808 wrote:
Villeroy wrote:Please stop this project. You must not use any kind of spreadsheet for this task. Sooner or later you will get unrecognized errors.


Thanks for the advise, but I am going to continue. If/when I start getting errors then I will adjust my needs. I prefer to not have negativity about things considering I am already stressed from running a 200k a year business alone. Quickbooks and other "financial" programs are all based off of spreadsheets so the mere thought of not using a spreadsheet for this is ignorant in itself.

Villeroy's usage of "unrecognized" is (I suspect) to the effect of "undetected". Don't think it won't happen. Sometimes, tiny numbers add up...

No offense intended. Undetected errors has happened to (otherwise brilliant) people before you. It has also happened to (not so brilliant) myself. A spreadsheet is excellent as a mathemathical modeling/prototyping tool, and for quick mockups. For long routine runs it is often unsafe.

That said, if you are confident about your use of the spreadsheet, and you are the only user, by all means proceed. All we can provide is advice. Use the tools best suited to your workflow.
User avatar
keme
Volunteer
 
Posts: 3209
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: FJCC and 30 guests