I want to make a worksheet which can be used as a printed receipt towards customers, this is the basic layout:
- Customer contact details (name, address, etc) these are fixed fields that may be empty
- Header row: "Item|Qty.|price|total"
- Dynamic table containing any number of lines, one for each article the customer purchases
- Footer row containing total number of articles and total amount
What I'm struggling with is how to make the dynamic table work.
Currently I manually insert a row for each new article, but this is cumbersome in situations where it's very busy.
I would like excel to show one blank line by default, and then insert a next line each time the previous line contains any data, automatically moving the footer row down one line and updating the formula used to calculate the total qty. and amount.
Is this possible at all?
Best Answer
Just as a warning to you and any other who attempt to do this:
Right now you should think about:
These are the most important questions. I have done quite exactly, what you are trying to do, for a very small company, with aroud 3 receipts per month.
As always, please fast & cheap & excelOnly. However, nobody asked those questions above. Mainly because of cheap & excelOnly. And now, it is so obvious how much time is lost, whenever a receipt is searched, corrected, but mainly when the items are added. Not to mention how many different forms of the same item exists. Correcting this with excel is however possible, but IMHO, a real pain. Convinced the cutomer too, an EPR is now setup.
This is a database task. It is like one of the best examples of a database task!
Further, this might even be worth a small business ERP-System. Here in Germany, I would recommend you JTL-Wawi.
But, if this is too big of a jump, you should really think about using Access.
Some advantages:
How this would work?
You would have some tables, for customers and items, and for receipts. The receipt table would connect a customer to his items and other receipt relevant attributes.
Then you could create a designed report for a basic receipt, and print it from access. Or use excel to select a receipt, get the data and print it. Because the data would be predetermined, you would know how many rows/cols are comming, and what their context is.
However - this wheel was already invented so many times, so you should look into the already existing ones ;)
Edit
A solution based on your behaviour request could use this attempt here:
https://stackoverflow.com/questions/12604274/excel-vba-is-there-a-textchanging-textchanged-or-a-similar-event-or-how-to/12604570#12604570
You could of course use the
Change
event instead and you might have to limit this behaviour to the maximum range of your dynamic list or to the worksheet of it.Or just make a macro acceible per shortkey to execute this line here:
You will need to determine the currently selected cell to have the
Target
range.