Multiple invoice creation with data in the body

MooLard

New Member
Joined
May 27, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

Mr Excel query sample data.xlsx
ABCDEFGHIJK
4ManagerCustomerProduct revenueServices revenueExtras revenueProduct rateServices rateExtras rateProduct commissionServices commissionExtras commission
5Manager oneCustomer A£3,852.00£4,969.00£719.003.0%10.0%15.0%£115.56£496.90£107.85
6Manager oneCustomer B£6,103.00£2,076.00£623.003.0%10.0%15.0%£183.09£207.60£93.45
7Manager oneCustomer C£1,362.00£6,330.00£121.003.0%10.0%15.0%£40.86£633.00£18.15
8Manager oneCustomer D£792.00£5,660.00£726.006.0%10.0%15.0%£47.52£566.00£108.90
9Manager oneCustomer E£8,623.00£5,372.00£379.003.0%10.0%15.0%£258.69£537.20£56.85
10Manager oneCustomer F£4,235.00£1,360.00£251.006.0%10.0%15.0%£254.10£136.00£37.65
11Manager oneCustomer G£3,579.00£920.00£481.003.0%10.0%15.0%£107.37£92.00£72.15
12Manager oneCustomer H£1,851.00£1,225.00£741.003.0%10.0%15.0%£55.53£122.50£111.15
13Manager oneCustomer I£6,535.00£5,698.00£518.003.0%10.0%15.0%£196.05£569.80£77.70
14Manager oneCustomer J£367.00£6,392.00£306.003.0%10.0%15.0%£11.01£639.20£45.90
15Manager oneCustomer K£8,062.00£6,032.00£464.003.0%4.0%10.0%£241.86£241.28£46.40
16Manager oneCustomer L£2,364.00£4,356.00£772.003.0%4.0%10.0%£70.92£174.24£77.20
17Manager twoCustomer M£414.00£4,516.00£653.003.0%10.0%20.5%£12.42£451.60£133.87
18Manager twoCustomer N£214.00£1,303.00£304.003.0%10.0%20.5%£6.42£130.30£62.32
19Manager twoCustomer O£4,969.00£504.00£829.003.0%10.0%20.5%£149.07£50.40£169.95
20Manager twoCustomer P£7,536.00£3,334.00£272.003.0%10.0%20.5%£226.08£333.40£55.76
21Manager twoCustomer Q£1,100.00£4,298.00£368.003.0%10.0%20.5%£33.00£429.80£75.44
22Manager twoCustomer R£5,733.00£2,443.00£595.003.0%10.0%20.5%£171.99£244.30£121.98
23Manager threeCustomer S£346.00£370.00£212.005.0%10.0%15.0%£17.30£37.00£31.80
24Manager threeCustomer T£4,515.00£4,545.00£175.005.0%10.0%15.0%£225.75£454.50£26.25
25Manager threeCustomer U£6,954.00£4,780.00£243.005.0%10.0%15.0%£347.70£478.00£36.45
26Manager threeCustomer V£997.00£3,444.00£830.005.0%10.0%15.0%£49.85£344.40£124.50
27Manager threeCustomer W£5,043.00£2,223.00£414.005.0%10.0%15.0%£252.15£222.30£62.10
28Manager threeCustomer X£3,108.00£2,220.00£612.005.0%10.0%15.0%£155.40£222.00£91.80
29Manager threeCustomer Y£5,207.00£3,322.00£268.005.0%10.0%15.0%£260.35£332.20£40.20
30Manager threeCustomer Z£5,088.00£5,325.00£351.005.0%10.0%15.0%£254.40£532.50£52.65
31Manager threeCustomer AA£2,928.00£3,667.00£387.005.0%10.0%15.0%£146.40£366.70£58.05
32Manager threeCustomer AB£2,514.00£1,601.00£475.005.0%10.0%15.0%£125.70£160.10£71.25
33Manager threeCustomer AC£821.00£5,521.00£693.005.0%10.0%15.0%£41.05£552.10£103.95
34Manager threeCustomer AD£6,839.00£5,414.00£696.005.0%10.0%15.0%£341.95£541.40£104.40
35Manager threeCustomer AE£7,772.00£2,766.00£739.005.0%10.0%15.0%£388.60£276.60£110.85
36Manager threeCustomer AF£7,354.00£2,457.00£511.005.0%10.0%15.0%£367.70£245.70£76.65
data
Cell Formulas
RangeFormula
I5:I36I5=[@[Product revenue]]*[@[Product rate]]
J5:J36J5=[@[Services revenue]]*[@[Services rate]]
K5:K36K5=[@[Extras revenue]]*[@[Extras rate]]


Here are the addresses:
Mr Excel query sample data.xlsx
ABCDEFGHI
6ManagerDescriptionDateAddress1Address2Address3Address4PostCodeVAT number
7Manager oneStatement for the month of May'2101/06/20214 St James StreetCastle HeadinghamHalsteadEssexCO9 3EJ657 925 324
8Manager twoStatement for the month of May'2101/06/20219 Bramall StreetHydeCheshireSK14 4SR183 446 878
9Manager threeStatement for the month of May'2101/06/202117 Victoria Road EastThornton CleveleysLancashireFY5 5HT327 687 158
addresses


And here is a template of what I am looking to create from the two:
Cell Formulas
RangeFormula
A9A9=addresses!B8
B11B11=addresses!C8
B13B13=addresses!I8
A15A15=addresses!A8
A16A16=addresses!D8
A17A17=addresses!E8
A18A18=addresses!F8
A19A19=addresses!G9
A20A20=addresses!H8
A24:J29A24=data!B17
H31:J31H31=SUM(H24:H30)
H34H34=SUM(H31:J31)
H35H35=H34*0.2
H36H36=H34*1.2


Thank you in advance,
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,141,073
Messages
5,704,135
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top