Multiple Statement Generation from Outside Source Spreadsheet

Pettie · Jan 20, 2017

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:

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

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

p45cal · Jan 21, 2017

Let's have a workbook with these 2 spreadsheets in, then we can play without having to set up our own, badly-guessed, versions of your workbook and sheets therein.

Hercules1946 · Jan 21, 2017

Pettie said:
Hey all!

Any help on any parts of the above will be greatly appreciated.

Without some sample spreadsheets to work with, Its difficult to see how best to tackle this type of problem.
However I do have a spreadsheet that I designed to extract Customer information from a dataset and transfer this onto a Service Invoice.
Basically, the formulae extract the details based on the requested Record key, and the information is replicated on the Service Invoice tab.
At the moment, its set up to work with a unique key that returns one record, but it can easily be adapted to return multiple records with a common key. (up to 10 as currently set up).
You may be able to incorporate some of these methods into your worksheets.

Pettie · Jan 24, 2017

Hi both,

Thanks for your speedy replies!

I have included a link ( https://app.box.com/s/d5zxaq3s1euflbph2n11efbnyefcyo2w (I can't post links)) to download a sample spreadsheet which contains 3 tabs. 1) The Distribution List, 2) The Statement itself, 3) Client Information (addresses, retained % etc.).

Hopefully this should explain a bit more what I mean, with particular reference to the VLOOKUP formulas.

Please bear in mind that these are all on the one spreadsheet, whereas on the live version each tab represents a different spreadsheet.
I've already had to move one of these spreadsheets to the same folder as the others due to the VLOOKUP losing the Source every time the spreadsheet was opened, can't seem to work around that but it's manageable, just not ideal.

Thanks guys.

Joe

Hercules1946 · Jan 25, 2017

Pettie said:
Hopefully this should explain a bit more what I mean, with particular reference to the VLOOKUP formulas.

Sorry - but this doesn't really add to your written explanation, as I can't see any formulae because they are "picture" views rather than the actual workbook. I also noticed that the data to support the statement document isn't included.
You should be able to work out how to use my attachment in #4 to extract the customer detail onto the invoice.
With regard to attachments, I think that this is granted when your posts total reaches 5 or 6. Click on "Go Advanced", scroll down to "Manage Attachments" and follow the prompts to upload and attach your workbook.

Pettie · Feb 7, 2017

Hi guys,

I've been able to use your attachment to help quite a bit, thank you very much for providing.

I've also managed to get my .xlsm to save to a .pdf with the file name chosen from values inside the spreadsheet. I've included the code I used here in case it's useful to anyone else in the future. This is assuming you link it to an ActiveX Command Button.

Code:

Private Sub CommandButton1_Click()
Dim WB As Workbook
    Dim SH As Worksheet
    Dim Rng As Range
    Dim sStr As String, aStr As String, sfile As String
    Dim i As Long, j As Long
    Const myPath As String = "[COLOR=#ff0000]C:\Users\NAME\Desktop[/COLOR]"       [COLOR=#ff0000]<------ Location you want file saved[/COLOR]
    Const myCell As String = "[COLOR=#ff0000]o14[/COLOR]"       [COLOR=#FF0000]<------ Cell which contains the desired file name[/COLOR]
    Const sSheetName As String = "[COLOR=#ff0000]Statement[/COLOR]"       [COLOR=#FF0000]<------ Name of tab which contains above cell[/COLOR]
    Const sExt As String = ".pdf"


    Set WB = ThisWorkbook
    Set SH = WB.Sheets(sSheetName)
    Set Rng = SH.Range(myCell)
    sStr = Rng.Value


    sfile = myPath & sStr & sExt


    If Len(Dir(sfile)) > 0 Then
        sStr = sStr & 1
        sfile = myPath & sStr & sExt
    End If


    SH.ExportAsFixedFormat Type:=xlTypePDF, _
                           Filename:=sfile, _
                           Quality:=xlQualityStandard, _
                           IncludeDocProperties:=True, _
                           IgnorePrintAreas:=False, _
                           OpenAfterPublish:=False
End Sub

Now the only issue I'm having is the exceptions list...

The Problem

Normally the statement looks up the client name and then applies the relevant fee % based on that name using a simple VLOOKUP. This works perfectly for 98% of my clients.

The other 2% are an exception to the simple rule. The 98% have one flat fee %, so always 10% for example. Whereas these exception people have a flat fee for all except certain territories.

So to cover this, I have created a second tab on my Statement and have called it the Exceptions. In that, I have listed the client name, the normal %, the exception % when it's changed and the country name which causes the % to change. It looks like this:

	A	B	C	D
1	Name	Normal %	Exception %	Country
2	Joe Bloggs	10	7.5	Germany
3	Adam Smith	10	5	UK
4	George Clooney	20	10	France

The Solution

What is needed is an additional layer to my current formula to vlookup the client's %.
For the 98% that are normal, the following formula works perfectly:

=VLOOKUP(B2,'[FILENAME.xlsx]Client List'!$A$2:$F$136,4,FALSE)

The above doesn't need to be altered, I believe it just needs to have a layer added to it saying along the lines of "If Client Name is included in the Exception List A column AND the Country Name matches the Exception List D column, then apply the Exception List C column %, otherwise the Exception List B column %". However to be complete, this formula needs to cover the 98% too, hence why it needs to check if the client is on the exception list first, and if not then ignore the exception rules and just apply the VLOOKUP % instead.

Tried So Far

I've been breaking it down into individual parts to work this formula out, so far it appears to work okay using Lookup but I can't seem to amalgamate this all into one working formula.

=LOOKUP(Statement!B2,A2:A4,D2: D4)

This formula looks up the Client Name entered onto the Statement tab (Statement!B2) and checks if it's in the Exception List A column (A2:A4), then if it is, it enters the country from the D column (D2: D4).
This is the first step of the overall formula.

I now need something which checks if the client name entered into Statement!B2 is in the Exception list (the above formula) and if the country entered matches that client's exception country. If so, apply the exception %, if not, apply the normal %.

The following formula works if I only had the one exception client:

=IF(Exceptions!C16=Statement!D18,Exceptions!C2,Exceptions!B2)
=IF(Outcome of Above LOOKUP Formula = Society Entered on Statement Tab, Apply Exception %, Otherwise Apply Normal %)

But as I have multiple, this doesn't work. So I need something which does what the above does, but for a range of clients not just the one.

Hercules1946 · Feb 8, 2017

Without an example workbook showing your current formulae, and the data its referencing, I don't think anyone is going to be able to construct a formula (for you) that does what you want.
I can give you some general advice that addresses the type of issue you have, however.
Basically you start with a VLOOKUP that works for most purposes, but there are exceptions. Based on what you say it seems that the exceptions can be dealt with by using a different LOOKUP.
So... Id start with a nested IF statement because these are ideal for deciding which way to go when there are choices.
To begin with I would try one list of Client %s and start with =IF(VLOOKUP2 = 0, VLOOKUP1, IF(VLOOKUP3 =COUNTRY, VLOOKUP2, LOOKUP1))

1. LOOKUP2 tests Col C for an Exception %
2 If 1. returns 0;
........2.1. LOOKUP1 picks up the Normal % for the majority of Clients
.... If 1. returns other;
.........2.2. LOOKUP3 Checks the COUNTRY against Col D
................. If 2.2 Matches;
.....................2.3 LOOKUP2 picks up Exception %
..................IF 2.2 No Match;
.....................2.4 LOOKUP1 picks up Standard %

p45cal · Feb 8, 2017

It really depends on how the various percentages are arrived at which is the best/a good way of doing this.
If, for example, a person's percentage is a simple proportion (or any calculation) of his default precentage, depending on the country (eg, for Germany it's always 50% of default percentage, for the Netherlands always three-quarters of his default percentage, then we could use a second lookup table.

If the percentages are impossible to calculate (they may each have been arrived at through negotiation, person by person, country be country, and so are completely arbitrary) then I would consider altering your client table on the Client Informatiuon sheet to add more columns. First change the header in cell C1 from % to USA and add further country names in row 1 and fill in perentages in the body of that table (you wouldn't have to fill them all). Then you could use an Index/Match,Match formula for all country/client combinations and you wouldn't have to deal with so-called exceptions. Your formula would be something along the lines of:

Code:

=$F14*INDEX('Client Information'!$A$1:$F$5,MATCH($B$2,'Client Information'!$A$1:$A$5,0),MATCH($D14,'Client Information'!$A$1:$F$1,0))

If there are going to be exceptions, then it's misleading to have a header showing just the default % on the statement sheet; why not a separate column showing the percentage retained on each row of that table?

Code:

=INDEX('Client Information'!$A$1:$F$5,MATCH($B$2,'Client  Information'!$A$1:$A$5,0),MATCH($D14,'Client  Information'!$A$1:$F$1,0))

and in the adjacent cell a simple multiplication (eg. =$F14* that cell)?

Pettie · Feb 8, 2017

Thank you both for your help! It really is appreciated.

One more post after this one and I'll be able to attach spreadsheets which should help.

To clarify the situation a little...

Let's say there are 100 clients.
90 of them are one single set % for all countries and so are incredibly simple, just a basic "=Cell*.1" works for them.
Then there are 10 clients who have a set % for all countries, except for one singular country each. So Joe Bloggs is 10% everywhere except for Germany where he's 5%, Adam Johnson is 10% everywhere except for France where he's 7.5% etc.

This is why the formula needs to match against two set conditions. Using the above example people again, if there is money for Joe Bloggs from Canada, France & Germany it would use 10% for the first two and then 5% for the third.

1. LOOKUP2 tests Col C for an Exception %
2 If 1. returns 0;
........2.1. LOOKUP1 picks up the Normal % for the majority of Clients
.... If 1. returns other;
.........2.2. LOOKUP3 Checks the COUNTRY against Col D
................. If 2.2 Matches;
.....................2.3 LOOKUP2 picks up Exception %
..................IF 2.2 No Match;
.....................2.4 LOOKUP1 picks up Standard %

Hercules1946: I'm uncertain how this formula begins, when you say "LOOKUP2 tests Column C for an Exception %?", is this looking at the Exceptions table and checking whether the user-inputted client name has an exception % or not?

If that is the case then with my current layout it wouldn't work as the Exceptions table only lists clients which have exceptions, if a client is just a flat % for all territories then they aren't on the list. Hence why 95% of the clients aren't on this list.
So in order to get the above code to work, I believe I would have to amalgamate all my clients together into the table and list two percentages for the people with exceptions and just one percentage for the normal clients. Is that what you're suggesting?

P45cal: If i'm understanding you right, you're saying to list out all of the countries horizontally and all the client names vertically then enter their relevant % for each territory so that you could use the intersection to immediately see what % to apply.
This would solve the issue as I would then use a formula to match the name and the country against the user inputted data and therefore apply the correct %.

The main issue with that is that 99% of the time it's just one set % rather than territory specific ones. So it ends up creating a large process for a very small minority of exceptions.

So bearing the above in mind, would your solution be instead to use a second lookup table?

p45cal · Feb 8, 2017

Pettie said:
One more post after this one and I'll be able to attach spreadsheets which should help.

While there is a requirement of 5 posts for links, there is no such restriction for attachments - anyway, your link in message #3 links to a workbook file.

Pettie said:
If i'm understanding you right, you're saying to list out all of the countries horizontally and all the client names vertically then enter their relevant % for each territory so that you could use the intersection to immediately see what % to apply.
This would solve the issue as I would then use a formula to match the name and the country against the user inputted data and therefore apply the correct %.

The main issue with that is that 99% of the time it's just one set % rather than territory specific ones. So it ends up creating a large process for a very small minority of exceptions.

So bearing the above in mind, would your solution be instead to use a second lookup table?

Not at all, because different clients have very different and individual percentages (you said:

Pettie said:
So Joe Bloggs is 10% everywhere except for Germany where he's 5%, Adam Johnson is 10% everywhere except for France where he's 7.5% etc.

) you have to have a system (a table seems to be easy and transparent) where you can pick out those values.

How many countries are we talking about?
You know it's very easy to put a single value into a large range of cells by selecting those cells, entering a value then pressing Ctrl+Enter? Then you'd amend the exception(s). If your default percentages change frequently, you could even have a formula in most of that table's cells linking to another cell or cells containg the default percentage(s) and overtype the formulae with direct entries for the exceptional values.

It's not really a 'large' process, it does however allow you to have a consistent formula, and keeps it (and the updating of the table) relatively simple and transparent.

Hercules1946 · Feb 9, 2017

Pettie said:
Hercules1946: I'm uncertain how this formula begins

I suppose Ive illustrated my own point about how difficult it is to explain/illustrate without an example

So.. Ive put together a simple example in the attachment that shows how to use multiple VLOOKUPs in a nested IF() statement, from one dataset:
Here is the formula used to find the correct fee %. The only difference between the lookups is the column number of the returned value (in bold red):

Code:

[TABLE]
[TR]
[TD="class: xl63"]=IF(AND(VLOOKUP(H2,$A$2:$D$5,[B][COLOR=red]3[/COLOR][/B],0)<>0,(VLOOKUP(H2,$A$2:$D$5,[B][COLOR=red]4[/COLOR][/B],0)=I2)),VLOOKUP(H2,$A$2:$D$5,[B][COLOR=red]3[/COLOR][/B],0),VLOOKUP(H2,$A$2:$D$5,[B][COLOR=red]2[/COLOR][/B],0))[/TD]
[/TR]
[/TABLE]

p45cal · Feb 9, 2017

Hercules1946 said:
a simple example in the attachment that shows how to use multiple VLOOKUPs in a nested IF() statement,

Yes, this is the way to go since it's much simpler to set up.
Be aware of 2 things:
1. That what you said: "clients who have a set % for all countries, except for one singular country each" always obtains and that no client ever has more than one country exception.
2. Be sure to spell the countries exactly the same, no leading/trailing spaces differences, because you will not get a warning (an error in the cell) that the country hasn't been found, you'll get the default percentage instead.

Hercules1946 · Feb 9, 2017

p45cal said:
Yes, this is the way to go since it's much simpler to set up.

Excellant additional advice. Points 1, 2. Without being able to look at the entire system, I've just tried to provide what is asked for. I'm sure that your right about business needing to be catered for in many countries (100 clients was it?)
We may have a better idea when Pettie posts his worksheet - otherwise watch this space

Pettie · Feb 16, 2017

Wow... Thank you both very much. You've both been incredibly helpful and now I can officially say that this issue is officially resolved!

In case you were curious what finally got it working...

I used Hercules1946's FeeLookup.xlsx (this was essential, I couldn't have done it without this spreadsheet!) and from that I could see how I needed to change my data.

When i posed the original question my client data was laid out as follows:

Spreadsheet 1
All Names | Normal %

Spreadsheet 2
Exception Names | Exception %

However after looking at your formula and seeing how it bases it on whether or not the Exception % column is "<>0" I could see how much easier it would be if I could amalgamate the two above spreadsheets into one.

So I inserted two new columns on Spreadsheet 1 so that each client has:

All Names | Normal % | Exception % | Country

Then vast majority of the clients had only the first two columns filled in and the next two blank, but the 10 or so clients with exceptions had all 4 filled in.
After that, I could just tweak the cell references and VLOOKUP provided by Hercules1946 to match my data and locations and then everything worked.

Now to get it to display correctly on the statement, rather than have every line look at the one set % for that client, it has this formula per line and individually works them out off screen.

So now this works perfectly, I'm incredibly happy and it's all thanks to the two of you!

Hercules1946 · Feb 16, 2017

Pettie said:
So now this works perfectly, I'm incredibly happy and it's all thanks to the two of you!

Thanks for the update! Getting this kind of response makes it all worth while!

Multiple Statement Generation from Outside Source Spreadsheet

Pettie

New member

p45cal

Super Moderator

Hercules1946

New member

Attachments

Pettie

New member

Hercules1946

New member

Pettie

New member

Hercules1946

New member

p45cal

Super Moderator

Pettie

New member

p45cal

Super Moderator

Hercules1946

New member

Attachments

p45cal

Super Moderator

Hercules1946

New member

Pettie

New member

Hercules1946

New member

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