Excel Geeks

[Nomoreheroes]

When you say it changes... There is normally a portion that identifies the direct debit itself and then a part that is the transaction number - are you able to compare a single members monthly payments to see if you can identify the breakdown of this?

Have just checked a sample over the last 3 months. Transactions are either:
SO (Which don't have a long number but are relatively static - Although changes to standing order payments are frequent, which often results in the payer amending the SO name)
FPI (Faster payment incoming - Which have what appears to be a fairly random alphanumeric pattern. I can't detect anything that appears to be constant)

[Nomoreheroes]

This is where the DB would manage things a lot better... You'd have a master table of members and, hanging off of a single ID from that you have two related tables - one for the dues and one for the payments... You'd then know exactly how much each member owes, how much they pay (through each SO) and how much is outstanding... 

But in this scenario (either in Excel or Access) I only know that they have paid £212.50 out of a total of £222.50. I wouldn't know which one is £10 short. I'd have to arbitrarily choose which one owed £10.

[Bewster]

If the alpha bit stays constant month on month then excel will do it for you. If everything changes then it won't. And I don't know enough about DBs to give an opinion, but I always thought you needed a link between the tables which is exactly what you are trying to do in excel.

[@MacPhlea]

It's the 'link' that gives the 'relation' in a relational database which, by simple deduction, implies you are better off using a database than a spreadsheet... However, NMH seems tied to excel so we see what we can do.

[@MacPhlea]

The other way to do it is to create a master worksheet for each customer with a unique ID then create a second worksheet/data set which links the SO name back to the master data with the amount paid - you could then sum all payments based on the master name without having to change the SO name to match.

[Bewster]

The other way to do it is to create a master worksheet for each customer with a unique ID then create a second worksheet/data set which links the SO name back to the master data with the amount paid - you could then sum all payments based on the master name without having to change the SO name to match.

this is what I was angling at on the previous page

[Nomoreheroes]

I'm back by popular demand !!!

Can I ask for some more help please?

I now have all 'swimmers' aligned to a 'squad' and there are standard fees for each squad. There are 10 squads.

Problem 1:
I have a list of swimmers in column A. When I select the squad in column B, I want to automatically put the squad cost in column C

e.g.
swimmer1 squad1 £10
swimmer2 squad2 £20
swimmer3 squad3 £30 etc etc

What formula should I use?

Problem 2:
We give a discount for some swimmers. For most swimmers there is a 0% discount. For some there is 10% and others 15% (eg. For siblings). So, in column D I want to put a % discount. Ideally, I would like that discount applied to the number in column C

e.g.
swimmer1 squad1 £10 0%
swimmer2 squad1 £9 10%
swimmer3 squad1 £8.50 15%

How do I do that?

Thanks chaps!

[Samdy Gray]

Solve both in one go. You need to set up a table with the squad rates and then in the cell where you want the cost to be, use a VLOOKUP to find the rate and then multiply it by the discount factor.

[Nomoreheroes]

Thanks Samdy. Have set up the table but am struggling with the syntax for the VLOOKUP. Can you help a bit more please?

I've got the following to do 'problem 1':

=IF(ISNA(VLOOKUP(B1,'Squad Fees'!A$2:B$147,2,FALSE)),0, VLOOKUP(B1,'Squad Fees'!A$2:B$147,2))

[sonicyouth]

Does the TEF do your job for you?

[Nomoreheroes]

Does the TEF do your job for you?

No it doesn't....But I'm hoping it will help me in this voluntary work helping a non profit making organisation that gets kids off the street and helps them take part in sport.

[sonicyouth]

Sounds like a waste of time to me.

[Loobug]

I you have a sheet with 4 columns (Swimmer, squad, discount & cost) and then set up a lookup table with (Squad 1, squad 2, etc with associate costs in the next column. Then you could use the following calculation. This requires you to manually type in the discount.

=VLOOKUP(B2,Sheet2!A1:B3,2,TRUE)-SUM(VLOOKUP(B2,Sheet2!A1:B3,2,TRUE)*C2)

I can send to you if it helps.

[Simon Pieman]

Squad fees would be just as easy to sort out with a nested IF function:

=IF(B2="Squad 1",10,IF(B2="Squad 2",20,IF(B2="Squad 3",30)))

If you have a discount column in column D (with a discount % [format the column as percentages for easier input]) then you could modify the above with:

=IF(B2="Squad 1",10*(1-D2),IF(B2="Squad 2",20*(1-D2),IF(B2="Squad 3",30*(1-D2))))


The only problem is if you get over 7 squads as the nested IF will only allow 7 arguments I think.

Would be more robust if you could only select the squad by a drop down menu. If you don't know how to do this then google it as it's quite simple to achieve.

[Simon Pieman]

Further investigation suggests you can have 64 nested IF arguments in a single string in Excel 2007 and later.