Pages: 1 2 3 4 [5]   Go Down
Print
Author Topic: Excel Geeks  (Read 17481 times)
Samdy Gray
Dirty sneaky traitor weasel

Offline Offline

Posts: 27137





Ignore
« Reply #60 on: Thursday, September 5, 2013, 13:51:44 »

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!
Logged
Simon Pieman
Original Wanker

Offline Offline

Posts: 36317




« Reply #61 on: Thursday, September 5, 2013, 13:57:26 »

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

Really needs to get a book on Excel.
Logged
Ardiles

Offline Offline

Posts: 11528


Stirlingshire Reds




Ignore
« Reply #62 on: Thursday, September 5, 2013, 15:53:06 »

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

Online Online

Posts: 14503





Ignore
« Reply #63 on: Thursday, September 5, 2013, 16:50:05 »

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!
Logged

You're my incurable malady. I miss the pleasure of your company.
Samdy Gray
Dirty sneaky traitor weasel

Offline Offline

Posts: 27137





Ignore
« Reply #64 on: Thursday, September 5, 2013, 17:22:43 »

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)

Logged
Nomoreheroes
The Moral Majority

Online Online

Posts: 14503





Ignore
« Reply #65 on: Thursday, September 5, 2013, 19:08:38 »

Once again thanks for this.

I was trying to overcomplicate things.
Logged

You're my incurable malady. I miss the pleasure of your company.
Nomoreheroes
The Moral Majority

Online Online

Posts: 14503





Ignore
« Reply #66 on: Sunday, September 8, 2013, 14:01:44 »

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
Logged

You're my incurable malady. I miss the pleasure of your company.
Samdy Gray
Dirty sneaky traitor weasel

Offline Offline

Posts: 27137





Ignore
« Reply #67 on: Sunday, September 8, 2013, 14:20:02 »

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
Logged
Nomoreheroes
The Moral Majority

Online Online

Posts: 14503





Ignore
« Reply #68 on: Sunday, September 8, 2013, 15:17:52 »

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.
Logged

You're my incurable malady. I miss the pleasure of your company.
Samdy Gray
Dirty sneaky traitor weasel

Offline Offline

Posts: 27137





Ignore
« Reply #69 on: Sunday, September 8, 2013, 15:44:50 »

No, you create the Mail Merge in Outlook and select the Excel spreadsheet as the source file.
Logged
Pages: 1 2 3 4 [5]   Go Up
Print
Jump to: