Hey all!
The Situation:
I am using Excel 2013 (on Windows 10) and I have a simple spreadsheet which lists names and amount payable plus some sundry columns, lets call it the Distribution spreadsheet.
I also have a separate spreadsheet as my Statement template which displays the data from the Distribution spreadsheet.
I have set up the Statement template already through VLOOKUP to automatically insert the client's address, method of payment etc. all based upon the cell containing "Client Name", and that's working basically fine.
The Idea:
I would like the Statement to populate itself based upon the contents of the Distribution spreadsheet.
So basically:
- Distribution spreadsheet has 2 clients on it this time.
- Statement spreadsheet should go to the Distribution spreadsheet and look at cell A2 to get the Client Name, then cells F2, G2 & H2 to gather the various figures, then I2 to get the date.
- Statement spreadsheet should then populate itself with A2 (which inserts the name, address etc. as stated above) as well as F2, G2 & H2 (the figures) and lastly I2.
- Then it saves this with a preset filename which is taken from values in the Statement spreadsheet (see example below)
- Then it repeats this process until it has no more names on the original Distribution spreadsheet.
Summary & Example:
<tbody>
</tbody>
So in this example, two statements should be generated, and they should have the filename of: "2017 1 (30th January) - Joe Bloggs £360.00.pdf" & "2017 1 (30th January) - Jimmy Lennon £180.00.pdf". In code form this would be "{YEAR} {MONTH}({DAY & MONTH}) - {NAME} £{PAYABLE}.pdf".
I can break this problem down into two parts:
1) The statement generation from the source (Distribution) spreadsheet.
2) Saving the Statement as a .pdf with the correct filename for each client.
1) I imagine this will be utilising VLOOKUP a lot to reference the Distribution spreadsheet's contents, presumably based around the Client's Name.
2) I imagine this will have to be done through a Macro assigned to a button on the spreadsheet.
Any help on any parts of the above will be greatly appreciated. I've been tearing my hair out trying to break this down into smaller, manageable parts but can't seem to be able to figure out the logic on this bit.
Thanks everyone!
Your friend,
Joe
The Situation:
I am using Excel 2013 (on Windows 10) and I have a simple spreadsheet which lists names and amount payable plus some sundry columns, lets call it the Distribution spreadsheet.
I also have a separate spreadsheet as my Statement template which displays the data from the Distribution spreadsheet.
I have set up the Statement template already through VLOOKUP to automatically insert the client's address, method of payment etc. all based upon the cell containing "Client Name", and that's working basically fine.
The Idea:
I would like the Statement to populate itself based upon the contents of the Distribution spreadsheet.
So basically:
- Distribution spreadsheet has 2 clients on it this time.
- Statement spreadsheet should go to the Distribution spreadsheet and look at cell A2 to get the Client Name, then cells F2, G2 & H2 to gather the various figures, then I2 to get the date.
- Statement spreadsheet should then populate itself with A2 (which inserts the name, address etc. as stated above) as well as F2, G2 & H2 (the figures) and lastly I2.
- Then it saves this with a preset filename which is taken from values in the Statement spreadsheet (see example below)
- Then it repeats this process until it has no more names on the original Distribution spreadsheet.
Summary & Example:
| A | B | C | D | E | F | G | H | I |
| Name | Country | Native Currency | GBP | Bank Charge | 100% | Retained | Payable | Date Out |
| Joe Bloggs | USA | $500 | £410 | £10 | £400 | £40 | £360 | 30th January 2017 |
| Jimmy Lennon | USA | $300 | £205 | £5 | £200 | £20 | £180 | 30th January 2017 |
<tbody>
</tbody>
So in this example, two statements should be generated, and they should have the filename of: "2017 1 (30th January) - Joe Bloggs £360.00.pdf" & "2017 1 (30th January) - Jimmy Lennon £180.00.pdf". In code form this would be "{YEAR} {MONTH}({DAY & MONTH}) - {NAME} £{PAYABLE}.pdf".
I can break this problem down into two parts:
1) The statement generation from the source (Distribution) spreadsheet.
2) Saving the Statement as a .pdf with the correct filename for each client.
1) I imagine this will be utilising VLOOKUP a lot to reference the Distribution spreadsheet's contents, presumably based around the Client's Name.
2) I imagine this will have to be done through a Macro assigned to a button on the spreadsheet.
Any help on any parts of the above will be greatly appreciated. I've been tearing my hair out trying to break this down into smaller, manageable parts but can't seem to be able to figure out the logic on this bit.
Thanks everyone!
Your friend,
Joe
