Hi there, it is time I broadened my average Excel skills in order to improve a monthly task that I do. At the moment I copy the prior month's invoices (120+) and then individually copy and update the data into each invoice. While this enables me to work closely with the data it is time consuming, so I would like to create a way of generating new invoices once the data and addresses are ready. It is ok for the separate invoices to be in Excel but I would like to understand what would be involved to save them as PDFs.
My current Excel usage is varying size datasets, power query for data manipulation but not VBA. I realise this might be the optimum route to do this so I hope someone can educate me clearly on how I could do this, or point me in the right direction.
Here is the data:
Here are the addresses:
And here is a template of what I am looking to create from the two:
Thank you in advance,
My current Excel usage is varying size datasets, power query for data manipulation but not VBA. I realise this might be the optimum route to do this so I hope someone can educate me clearly on how I could do this, or point me in the right direction.
Here is the data:
| Mr Excel query sample data.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 4 | Manager | Customer | Product revenue | Services revenue | Extras revenue | Product rate | Services rate | Extras rate | Product commission | Services commission | Extras commission | ||
| 5 | Manager one | Customer A | £3,852.00 | £4,969.00 | £719.00 | 3.0% | 10.0% | 15.0% | £115.56 | £496.90 | £107.85 | ||
| 6 | Manager one | Customer B | £6,103.00 | £2,076.00 | £623.00 | 3.0% | 10.0% | 15.0% | £183.09 | £207.60 | £93.45 | ||
| 7 | Manager one | Customer C | £1,362.00 | £6,330.00 | £121.00 | 3.0% | 10.0% | 15.0% | £40.86 | £633.00 | £18.15 | ||
| 8 | Manager one | Customer D | £792.00 | £5,660.00 | £726.00 | 6.0% | 10.0% | 15.0% | £47.52 | £566.00 | £108.90 | ||
| 9 | Manager one | Customer E | £8,623.00 | £5,372.00 | £379.00 | 3.0% | 10.0% | 15.0% | £258.69 | £537.20 | £56.85 | ||
| 10 | Manager one | Customer F | £4,235.00 | £1,360.00 | £251.00 | 6.0% | 10.0% | 15.0% | £254.10 | £136.00 | £37.65 | ||
| 11 | Manager one | Customer G | £3,579.00 | £920.00 | £481.00 | 3.0% | 10.0% | 15.0% | £107.37 | £92.00 | £72.15 | ||
| 12 | Manager one | Customer H | £1,851.00 | £1,225.00 | £741.00 | 3.0% | 10.0% | 15.0% | £55.53 | £122.50 | £111.15 | ||
| 13 | Manager one | Customer I | £6,535.00 | £5,698.00 | £518.00 | 3.0% | 10.0% | 15.0% | £196.05 | £569.80 | £77.70 | ||
| 14 | Manager one | Customer J | £367.00 | £6,392.00 | £306.00 | 3.0% | 10.0% | 15.0% | £11.01 | £639.20 | £45.90 | ||
| 15 | Manager one | Customer K | £8,062.00 | £6,032.00 | £464.00 | 3.0% | 4.0% | 10.0% | £241.86 | £241.28 | £46.40 | ||
| 16 | Manager one | Customer L | £2,364.00 | £4,356.00 | £772.00 | 3.0% | 4.0% | 10.0% | £70.92 | £174.24 | £77.20 | ||
| 17 | Manager two | Customer M | £414.00 | £4,516.00 | £653.00 | 3.0% | 10.0% | 20.5% | £12.42 | £451.60 | £133.87 | ||
| 18 | Manager two | Customer N | £214.00 | £1,303.00 | £304.00 | 3.0% | 10.0% | 20.5% | £6.42 | £130.30 | £62.32 | ||
| 19 | Manager two | Customer O | £4,969.00 | £504.00 | £829.00 | 3.0% | 10.0% | 20.5% | £149.07 | £50.40 | £169.95 | ||
| 20 | Manager two | Customer P | £7,536.00 | £3,334.00 | £272.00 | 3.0% | 10.0% | 20.5% | £226.08 | £333.40 | £55.76 | ||
| 21 | Manager two | Customer Q | £1,100.00 | £4,298.00 | £368.00 | 3.0% | 10.0% | 20.5% | £33.00 | £429.80 | £75.44 | ||
| 22 | Manager two | Customer R | £5,733.00 | £2,443.00 | £595.00 | 3.0% | 10.0% | 20.5% | £171.99 | £244.30 | £121.98 | ||
| 23 | Manager three | Customer S | £346.00 | £370.00 | £212.00 | 5.0% | 10.0% | 15.0% | £17.30 | £37.00 | £31.80 | ||
| 24 | Manager three | Customer T | £4,515.00 | £4,545.00 | £175.00 | 5.0% | 10.0% | 15.0% | £225.75 | £454.50 | £26.25 | ||
| 25 | Manager three | Customer U | £6,954.00 | £4,780.00 | £243.00 | 5.0% | 10.0% | 15.0% | £347.70 | £478.00 | £36.45 | ||
| 26 | Manager three | Customer V | £997.00 | £3,444.00 | £830.00 | 5.0% | 10.0% | 15.0% | £49.85 | £344.40 | £124.50 | ||
| 27 | Manager three | Customer W | £5,043.00 | £2,223.00 | £414.00 | 5.0% | 10.0% | 15.0% | £252.15 | £222.30 | £62.10 | ||
| 28 | Manager three | Customer X | £3,108.00 | £2,220.00 | £612.00 | 5.0% | 10.0% | 15.0% | £155.40 | £222.00 | £91.80 | ||
| 29 | Manager three | Customer Y | £5,207.00 | £3,322.00 | £268.00 | 5.0% | 10.0% | 15.0% | £260.35 | £332.20 | £40.20 | ||
| 30 | Manager three | Customer Z | £5,088.00 | £5,325.00 | £351.00 | 5.0% | 10.0% | 15.0% | £254.40 | £532.50 | £52.65 | ||
| 31 | Manager three | Customer AA | £2,928.00 | £3,667.00 | £387.00 | 5.0% | 10.0% | 15.0% | £146.40 | £366.70 | £58.05 | ||
| 32 | Manager three | Customer AB | £2,514.00 | £1,601.00 | £475.00 | 5.0% | 10.0% | 15.0% | £125.70 | £160.10 | £71.25 | ||
| 33 | Manager three | Customer AC | £821.00 | £5,521.00 | £693.00 | 5.0% | 10.0% | 15.0% | £41.05 | £552.10 | £103.95 | ||
| 34 | Manager three | Customer AD | £6,839.00 | £5,414.00 | £696.00 | 5.0% | 10.0% | 15.0% | £341.95 | £541.40 | £104.40 | ||
| 35 | Manager three | Customer AE | £7,772.00 | £2,766.00 | £739.00 | 5.0% | 10.0% | 15.0% | £388.60 | £276.60 | £110.85 | ||
| 36 | Manager three | Customer AF | £7,354.00 | £2,457.00 | £511.00 | 5.0% | 10.0% | 15.0% | £367.70 | £245.70 | £76.65 | ||
data | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I5:I36 | I5 | =[@[Product revenue]]*[@[Product rate]] |
| J5:J36 | J5 | =[@[Services revenue]]*[@[Services rate]] |
| K5:K36 | K5 | =[@[Extras revenue]]*[@[Extras rate]] |
Here are the addresses:
| Mr Excel query sample data.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 6 | Manager | Description | Date | Address1 | Address2 | Address3 | Address4 | PostCode | VAT number | ||
| 7 | Manager one | Statement for the month of May'21 | 01/06/2021 | 4 St James Street | Castle Headingham | Halstead | Essex | CO9 3EJ | 657 925 324 | ||
| 8 | Manager two | Statement for the month of May'21 | 01/06/2021 | 9 Bramall Street | Hyde | Cheshire | SK14 4SR | 183 446 878 | |||
| 9 | Manager three | Statement for the month of May'21 | 01/06/2021 | 17 Victoria Road East | Thornton Cleveleys | Lancashire | FY5 5HT | 327 687 158 | |||
addresses | |||||||||||
And here is a template of what I am looking to create from the two:
| Mr Excel query sample data.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 4 | ||||||||||||
| 5 | ||||||||||||
| 6 | ||||||||||||
| 7 | ||||||||||||
| 8 | ||||||||||||
| 9 | Statement for the month of May'21 | |||||||||||
| 10 | ||||||||||||
| 11 | Date | 01/06/2021 | ||||||||||
| 12 | ||||||||||||
| 13 | VAT number | 183 446 878 | ||||||||||
| 14 | ||||||||||||
| 15 | Manager two | |||||||||||
| 16 | 9 Bramall Street | |||||||||||
| 17 | Hyde | |||||||||||
| 18 | Cheshire | |||||||||||
| 19 | 0 | * what is a suitable formula to make this cell bank when there is no data to insert? | ||||||||||
| 20 | SK14 4SR | |||||||||||
| 21 | ||||||||||||
| 22 | ||||||||||||
| 23 | Customer | Product revenue | Services revenue | Extras revenue | Product rate | Services rate | Extras rate | Product commission | Services commission | Extras commission | ||
| 24 | Customer M | £414.00 | £4,516.00 | £653.00 | 3.0% | 10.0% | 20.5% | £12.42 | £451.60 | £133.87 | ||
| 25 | Customer N | £214.00 | £1,303.00 | £304.00 | 3.0% | 10.0% | 20.5% | £6.42 | £130.30 | £62.32 | ||
| 26 | Customer O | £4,969.00 | £504.00 | £829.00 | 3.0% | 10.0% | 20.5% | £149.07 | £50.40 | £169.95 | ||
| 27 | Customer P | £7,536.00 | £3,334.00 | £272.00 | 3.0% | 10.0% | 20.5% | £226.08 | £333.40 | £55.76 | ||
| 28 | Customer Q | £1,100.00 | £4,298.00 | £368.00 | 3.0% | 10.0% | 20.5% | £33.00 | £429.80 | £75.44 | ||
| 29 | Customer R | £5,733.00 | £2,443.00 | £595.00 | 3.0% | 10.0% | 20.5% | £171.99 | £244.30 | £121.98 | ||
| 30 | ||||||||||||
| 31 | Sub-totals | £598.98 | £1,639.80 | £619.31 | ||||||||
| 32 | ||||||||||||
| 33 | ||||||||||||
| 34 | Net total | £2,858.09 | ||||||||||
| 35 | VAT | £571.62 | ||||||||||
| 36 | Gross total | £3,429.70 | ||||||||||
template | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A9 | A9 | =addresses!B8 |
| B11 | B11 | =addresses!C8 |
| B13 | B13 | =addresses!I8 |
| A15 | A15 | =addresses!A8 |
| A16 | A16 | =addresses!D8 |
| A17 | A17 | =addresses!E8 |
| A18 | A18 | =addresses!F8 |
| A19 | A19 | =addresses!G9 |
| A20 | A20 | =addresses!H8 |
| A24:J29 | A24 | =data!B17 |
| H31:J31 | H31 | =SUM(H24:H30) |
| H34 | H34 | =SUM(H31:J31) |
| H35 | H35 | =H34*0.2 |
| H36 | H36 | =H34*1.2 |
Thank you in advance,
