Multiple Fields to Multiple Rows (for want of a better description).

Hi all,

This is my first post to this forum. I don't generally need help on Excel, but I'm having to swallow my pride on this one.

Please see the attached xlsx. The basic background is: a mate asked me to build him a spreadsheet for his new Satellite TV equipment supplying/installation business. I'm having trouble arranging the data in such a way that it can create a printable invoice (see =Invoice!, note the drop down box top right), but also allow me to run pivottables on it. Note that the data in there is dummy data, but it should be realistic enough.


Take a look at the =Sales! and note that it currently has 5 "sections" of columns headed "Invoice Line 1 - 5". My mate wanted it this way for ease of data entry but it's causing me a headache. I am ideally after some kind of macro or piece of magic that can group each of these sections into one continuous table (i.e. multiple rows per invoice) on another sheet. This would enable me to run pivots on it to find such data as profit/product, profit/supplier, profit/customer and anything else I want.

Any advice or alternative solutions to the one I've suggested would be much appreciated. That sheet should show that I'm fairly competent with Excel, but constructive criticism is always welcome.


Thanks,

Jay