Formula help for invoice to statement

[erikashae808]

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.

[MrProgrammer]

Hi, and welcome to the forum.

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.

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

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

[RusselB]

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.

[erikashae808]

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

[RusselB]

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.

[RusselB]

Where is the invoice information coming from? Without that, any advice or suggestions would be a waste of time.

[erikashae808]

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.

[RusselB]

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.

[erikashae808]

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

[erikashae808]

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.

[RusselB]

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.

[erikashae808]

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.

[RusselB]

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.

[Villeroy]

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

[erikashae808]

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.

[RoryOF]

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.

[keme]

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.