Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
Have you tried making the items table a list? A list adds a new row to the bottom of the table when you start typing in it. (You know there are tons of invoice templates, which do everything you just described, on the Internet for free.) –  CharlieRB Nov 1 '12 at 12:25
    
Using a List (now called Tables) is definitely the best option. CharlieRB ought to make this an answer so you can mark it as accepted. –  AdamV Nov 5 '12 at 13:18

1 Answer 1

Just as a warning to you and any other who attempt to do this:

Right now you should think about:

  • how often will I use this per customer?
  • how many customers are there?
  • how will I input these Items?
  • how many different Items are there?

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:

  • you can manage your customers
  • you can manage your items
  • you can move your dymanic behaviour to access
  • you can manage your receipts easily
  • you can still use excel as your output system

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:

http://stackoverflow.com/questions/12604274/excel-vba-is-there-a-textchanging-textchanged-or-a-similar-event-or-how-to/12604570#12604570

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = clngColumnRightToLastGrade Then
    Application.EnableEvents = False
    'offset selection, one row down, two cols to left
    Target.EntireRow.Insert (xlShiftDown) 'insert new row
    Target.Offset(1, -2).Select
    Application.EnableEvents = True
  End If
End Sub

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:

Target.EntireRow.Insert (xlShiftDown) 'insert new row

You will need to determine the currently selected cell to have the Target range.

share|improve this answer
    
I actually work for a company which develops a POS system for fuel stations, so I do know that excel is very limited compared to full-blown POS solution. Let me explain what I do and what I need: I am a professional photographer. Now with Christmas coming up, I do a lot of events where I will take pictures that will be printed as christmas cards. But due to the large volume it's hard to do this on location. So I basically want to print just a small note to the customer, confirming what they have ordered and when it will be ready for pickup. –  Alex Nov 1 '12 at 13:44
    
@Alex - hope my edit helps you for now to get things started ;) –  Jook Nov 1 '12 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.