Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old May 18th 06, 08:15 AM posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default League table automatic sort/update

Hi Bob, Max

Returning zero for a match that has still to be played is the desired
result. Thanks to both of you for your help on this it's much
appreciated. Just getting a feeling for how powerful some of these
Excel features really are.

Regards
RH


  #22   Report Post  
Old May 18th 06, 08:59 AM posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default League table automatic sort/update

Here is one way around it, bit convoluted but it works.

First, hide zero values by going to ToolsoptionsView and unchecking the
Zero Values checkbox.

Then format the cells in results table like so

[<0.5]0;General

And finally, use this version of the formula

=SUMPRODUCT(--((($A$1:$A$20=$J2)*($B$1:$B$20$C$1:$C$20))+(($D$1 :$D$20=$J2)*
($B$1:$B$20<$C$1:$C$20))),--($E$1:$E$20=K$1))+

SUMPRODUCT(--((($A$1:$A$20=$J2)*($B$1:$B$20<$C$1:$C$20))+(($D$1 :$D$20=$J2)*(
$B$1:$B$20$C$1:$C$20))),--($E$1:$E$20=K$1))*0.0001+

SUMPRODUCT(--(($A$1:$A$20=$J2)+($D$1:$D$20=$J2)),--($E$1:$E$20=K$1),--($B$1:
$B$20=$C$1:$C$20))/2
--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" wrote in message
oups.com...
Hi Bob, Max

Returning zero for a match that has still to be played is the desired
result. Thanks to both of you for your help on this it's much
appreciated. Just getting a feeling for how powerful some of these
Excel features really are.

Regards
RH



  #23   Report Post  
Old May 18th 06, 02:15 PM posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default League table automatic sort/update

"Bob Phillips" wrote:
Here is one way around it, bit convoluted but it works.
....


Thought it was simply dazzling, Bob !
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #24   Report Post  
Old May 18th 06, 02:44 PM posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default League table automatic sort/update

Dazzling, maybe, but it does work <G

Regards

Bob

"Max" wrote in message
...
"Bob Phillips" wrote:
Here is one way around it, bit convoluted but it works.
....


Thought it was simply dazzling, Bob !
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Linking table in Excel to word travis Links and Linking in Excel 1 November 19th 05 03:30 PM
pivot table sort entries that don't yet appear in table rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM
Pivot Tables, Help? Adam Excel Discussion (Misc queries) 6 March 24th 05 03:35 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM


All times are GMT +1. The time now is 12:48 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017