Excel formula - creating invoices from data list

Question

I have an Excel spreadsheet with two tabs - one tab is a list of sales data (eg. date, amount, customer name, invoice number) and the other tab is where I want to make very very simply invoices (they aren't actually being given out).

Each invoice is just 11 rows (eg. the first few rows are company name, address etc). For the date, its =data!C6, for the invoice number its =data!H6, for the amount its =data!G6.

When I highlight the invoice (11 rows, 8 columns) and use the little box at the bottom right corner to drag down to generate the other invoices automatically, it works BUT the problem is Excel is getting the wrong values from the data tab.

For example, the next invoice should be =data!C7 / =data!H7 / =data!G7 but instead its getting the data =data!C17 / =data!H17 / =data!G17

Can someone please help me so that I can make it get the data one row at a time from the data tab?

Answer 1

If I understand your question correctly, it is :

Sheet1 = 1 data per line(row)
Sheet2 = 1 data per 11 lines(rows)  <-- desired output.

What I propose is, instead of direct using direct link from Sheet1 --> Sheet2, try to use "excel index match method".

It took me quite a while to figure it out, but it is worth doing.

1) Prepare data :

• Beside your 11x8 table at sheet2, put a running number.( it can be "hidden" for printing by r-click > text colour = white later, for now leave it as usual)
• for data at sheet1 add a "runnung number" column, at the left of your data. say 1 for data at line1, 2 for data at line2, and so on.
• for example sake, I'll assume at sheet1 : column1 = running number, column2 = date,column3 = amount,column4 = customer name,column5 = invoice number.
• l'll so assume that the customer data start at row 3. So 1st line data will be A3=1,B3=date1,C3=amount1,D3=name1,E3= invoice1. And 2nd date will be A4=2,B4=date2,C4=amount2,D4=name2,E4= invoice2 , and so on.
• and at sheet2 : A1:K8 is your intended data/format for invoice1, with "1" (number 1) was typed at L1 . and A9:K16 is invoice2, "=L1+1" is typed at L9 cell.

2) Load sheet1 data to sheet2

Use "index match method" at sheet2.

• Since A1:K8 is the data area for invoice1. and we want to put type the data at sheet2 this way : H2=date1,E4=amount1,C3=name1,H1= invoice1
• so at sheet2,H2 type : =index(<array2>,match(L1,<array1>,0)) ,at sheet2,E4 type : =index(<array3>,match(L1,<array2>,0)) ,at sheet2,C3 type : =index(<array4>,match(L1,<array2>,0)) ,at sheet2,H1 type : =index(<array5>,match(L1,<array2>,0))

• where : is sheet1,$A$3:$A$200, is sheet1,$B$3:$B$200, is sheet1,$C$3:$C$200, is sheet1,$D$3:$D$200, is sheet1,$E$3:$E$200.

• we'll espect to see the cell H2 will have date1, the cell E4 will have amount1, the cell C3 will have name1, the cell H1 will have invoice1 .

3) Extend it to the next rows

• at sheet2, select A1:K8, drag the little box at the bottom right corner until K16 . Note that we exclude L1 in this selection.
• now select A9:L16, drag the little box at the bottom right corner until K1600 . Note : L9 (running number) is now selected and dragged too.

Done.

We'll espect to see A1:K1600 is the invoice. L1 is 1, L9 is 2, L25 is 3. to "hide" L column L in the printing, just r-click > (icon)text colour = white .

Hope that helps. (: