Excel Geeks

[Samdy Gray]

Why fuck about with nested IF statements when VLOOKUP would be so much neater and easier.
 
With 10 squads, I wouldn't want to be troubleshooting that nested IF when it stops working!

[Simon Pieman]

I assume he understands IF functions and clearly doesn't understand VLOOKUPS.

Really needs to get a book on Excel.

[Ardiles]

This thread takes me right back to the heady days of the mid 1980s and writing programmes in BASIC for my ZX Spectrum.

[Nomoreheroes]

Thanks for all of your help thus far.

I considered using 'IF' functions, but thought the string would get very long with potential for 20-25 squads.

I'm not great with VLOOKUP, especially when combined with ISNA and other functions, which I also need.

My state of play at the moment is that I have used:

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

This checks my look up table and puts a numerical value in column C

I manually enter a percentage in column D and then have the following in column E:
=SUM(C1-(C1*D1))

This takes a percentage value from column D and calculates the discount which it places in column E. 

I cant work out how to combine both the long VLOOKUP and SUM together though - But then I'm no Excel expert!

[Samdy Gray]

Do you need the ISNA? Are the Squad fees ever blank? If I'm reading that formula right, you've got an IF statement that's returning the same results for both the 'then' and 'else' parts, just putting a 0 if the cell in Squad fees is blank.

If you want to do the lookup and sum in one go:

=VLOOKUP(B1,'Squad Fees'!A$1:B$147,2,FALSE)*(1-D1)

[Nomoreheroes]

Once again thanks for this.

I was trying to overcomplicate things.

[Nomoreheroes]

Another plea for help.

Club has just made wholesale changes to squad structures and fees. When reconciling using the excel skills that you have taught me I find that there are over 100 people that I need to email and tell them something simple like:

'Your new fees are £x but you have only paid £y. Please arrange for the remaining £z to be paid within 7 days.'

I'm really tired after a nightshift and before I waste my energy in researching, can someone tell me if it is possible to get Excel to automatically create 100 (or so emails) with a message along these lines? (I have X, Y & Z in columns in my spreadsheet and can create another reference sheet with everyone's emails. I also already use Outlook).

If it is possible, can someone help steer in in the right direction please?

Thanks

[Samdy Gray]

http://office.microsoft.com/en-001/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx

[Nomoreheroes]

http://office.microsoft.com/en-001/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx

Thanks. So does that mean I need to transfer from Excel to Word first? I was hoping that I could put in a script that read the value of a cell and then automatically created the email.

[Samdy Gray]

No, you create the Mail Merge in Outlook and select the Excel spreadsheet as the source file.