Excel 2007: Automatically insert row if previous row is filled in

microsoft excel

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:

  • 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:

https://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.

Related Question