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

**JayFa** · 01-25-2010, 08:48 PM

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

**Palmetto** · 01-25-2010, 09:55 PM

I would consider creating a custom user form for data entry, allowing multiple entries and then write the data to a database structure that facilitates use of Pivot Tables, e.g. a single column each for Product, price, cost, profit, etc. versus the current five colums each for Product, etc.

The custom form approach presents a cleaner interface, improvese ease of data entry and mitigates the necessity of scrolling across a wide range of cells currently required to acomplish data entry and it gives you flexibility of creating the right structure for analysis and reports.

**JayFa** · 01-26-2010, 04:57 AM

Thanks for the reply Palmetto.

I've found the section on this website giving advice on data entry forms and I'm flicking through my For Dummies reference. I'll take a look through it and see how I get on. Thanks.

I agree with the structure for producing Pivots, however this would make it hard to produce a printable invoice such as the one I've semi-completed. I'm guessing there is also a way of producing something better in VBA, but again it's beyond me. I'll have a play with it, but in the meantime if anyone can offer any advice on this, I'd appreciate it.

Also, I've only ever dealt with Excel for companies I've been working for. I've never done anything freelance like this. I've had someone else ask me to do something similar for them. Could anyone give me an idea of what a going rate would be for something like this?

Thanks all,

Jay

**Palmetto** · 01-26-2010, 09:12 AM

I've found the section on this website giving advice on data entry forms and I'm flicking through my For Dummies reference. I'll take a look through it and see how I get on. Thanks.

Well, I suggested a user form, but you could simply use another worksheet to mimic a form - it is less work overall and may be a better choice for your situation. As for pulling data into the invoice for printing, this could be easily done using Advanced Filter or Auto-Filter and copying visible cells.