Newbie

[Pegleg]

I don't use Excel to any great extent, but I'm getting into it.

I have this little problem that I cannot figure out.

ON this page (web) http://teetyme.com/outdoorsims/teamscores_april2.htm

I wish to add another set of cells that reflect the totals of the teams scores, and then sort those cells. I can sort each team fine. Sorting on Total of each player works great. I can make the additional cells and get the correct total for each team to appear there using a (SUM) formula.

Now here's my problem.... When I sort the new cells with the team totals to show which team is leading, it will only work the first sort. If I sort again, the formula has changed and I get different numbers as results. When I check the formula, it is n't the same as I originally enter. The range has changed. It'll do the same if I just use total1+total2+total 3.

The sheet being displayed now works fine. And it will work after I add the additional total cells. The thing that doesn't work is the sort on the new tabl of Team toals, which does not appear on the web site, since I cannot get it to work.

To explain it better:

I want to sort the players totals in a decending sort. I want the lower rh cell in each table to reflect the Sum of all the players 3 day totals. That part works. What I need to do is take the Team Totals and make a new table reflecting the team name and it's total, then sort that table on the team totals in a decending sort. When I try it, it works one time, the next time the formula has changed to a different range.

I hope I explained this right.

[ This Message was edited by: Pegleg on 2002-04-26 21:17 ]

[Chris Davison]

Hi Pegleg,

I'd go with =RANK and =VLOOKUP, so you don't have to keep sorting each time...

something like this maybe :

	A	B	C	D	E	F	G	H	I	J
1	=RANK(C1,$C$1:$C$8,0)	Team Blue	255	*	1	=VLOOKUP(E1,$A$1:$C$8,2,0)	=VLOOKUP(E1,$A$1:$C$8,3,0)	{--- copy down	*	*
2	=RANK(C2,$C$1:$C$8,0)	Team Red	326	*	2	=VLOOKUP(E2,$A$1:$C$8,2,0)	=VLOOKUP(E2,$A$1:$C$8,3,0)	*	*	*
3	=RANK(C3,$C$1:$C$8,0)	Team Green	612	*	3	=VLOOKUP(E3,$A$1:$C$8,2,0)	=VLOOKUP(E3,$A$1:$C$8,3,0)	*	*	*
4	=RANK(C4,$C$1:$C$8,0)	Team Yellow	554	*	4	=VLOOKUP(E4,$A$1:$C$8,2,0)	=VLOOKUP(E4,$A$1:$C$8,3,0)	*	*	*
5	=RANK(C5,$C$1:$C$8,0)	Team Brown	812	*	5	=VLOOKUP(E5,$A$1:$C$8,2,0)	=VLOOKUP(E5,$A$1:$C$8,3,0)	*	*	*
6	=RANK(C6,$C$1:$C$8,0)	Team Orange	362	*	6	=VLOOKUP(E6,$A$1:$C$8,2,0)	=VLOOKUP(E6,$A$1:$C$8,3,0)	*	*	*
7	=RANK(C7,$C$1:$C$8,0)	Team Black	222	*	7	=VLOOKUP(E7,$A$1:$C$8,2,0)	=VLOOKUP(E7,$A$1:$C$8,3,0)	*	*	*
8	=RANK(C8,$C$1:$C$8,0)	Team Purple	226	*	8	=VLOOKUP(E8,$A$1:$C$8,2,0)	=VLOOKUP(E8,$A$1:$C$8,3,0)	*	*	*
9	*	*	(linked from their total)	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*

which'll look something like this :

	A	B	C	D	E	F	G	H	I	J
1	6	Team Blue	255	*	1	Team Brown	812	*	*	*
2	5	Team Red	326	*	2	Team Green	612	*	*	*
3	2	Team Green	612	*	3	Team Yellow	554	*	*	*
4	3	Team Yellow	554	*	4	Team Orange	362	*	*	*
5	1	Team Brown	812	*	5	Team Red	326	*	*	*
6	4	Team Orange	362	*	6	Team Blue	255	*	*	*
7	8	Team Black	222	*	7	Team Purple	226	*	*	*
8	7	Team Purple	226	*	8	Team Black	222	*	*	*
9	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*

_________________
Hope this helps,
Chris


[ This Message was edited by: Chris Davison on 2002-04-26 22:08 ]

[Pegleg]

Hmmmm, looks a little complicated for a person of my skill level. But, let me play with it. I thank you for taking the time to reply.

My other alternative is converting to a mySQL/PHP page for it, since my server doesn't support Access.

[Chris Davison]

yeah, just enter the formula in a1 and copy down to a8, ditto the formulas in f1 and g1 and you're all set....

it'll resort itself as the totals change which is the handy thing

ps - team yellow needs to change it's bait or something !

[Pegleg]

Yea, team Yellow is on shore drinking all the beer, LOL.

After reading what you said, a light went off in my head! "LINK"

So I added the table and used "=Sheet1!K12" etc and the darn thing worked. You can see it using my link again:

http://teetyme.com/outdoorsims/teamscores_april2.htm

I do, however like what you have done, and I'm gonna try to implement it. Or, I can use macros, once I learn how to link macros to run one after another, or use one large macro.

I was dreading writing the php code for this thing... Guess I don't have to NOW!

Question: Using your method, do both new tables show up on the page?

[Joe Was]

Chris,

How did you post the screen prints?

[Pegleg]

OK, Chris, got it to work with macros. Did a macro for each team, then a macro for the Leaderboard. Then combined them using the editor. Works great. I can enter the scores, then press one key to run the combined macro, hit save and I'm done.

Thanks for waking up my brain!

[Chris Davison]

On 2002-04-27 00:07, Joe Was wrote:
Chris,

How did you post the screen prints?

Hi Joe,

they're not actually screen prints as such (although that makes me a smile as it shows my design is pretty good!)

it's actually a table in HTML (this board allows HTML commands) that I've automated using excel.... if you want the file (it's only small) repost and I'll send, although I only finished it this morning so will probably fiddle around more over the weekend. First sheet is your spreadhseet, 2nd sheet is the resulting HTML which you just copy into the board message...

Ivan's also looks good, with colours n stuff, I'd recommend checking his link out first though :

http://www.mrexcel.com/board/viewtop...11&forum=10&10

[Chris Davison]

On 2002-04-27 00:14, Pegleg wrote:
OK, Chris, got it to work with macros. Did a macro for each team, then a macro for the Leaderboard. Then combined them using the editor. Works great. I can enter the scores, then press one key to run the combined macro, hit save and I'm done.

Thanks for waking up my brain!

No worries ! Site looks good too

(my personal best was a 2lb 2oz Pike and it was <---------------------------------- this -------------------------------> long)



[ This Message was edited by: Chris Davison on 2002-04-27 05:56 ]

[Pegleg]

This long huh, lol. Thanks for the kind words regarding the site. Now get this, the site is for a pc game called Trophy Bass 4, a virtual fishing game. (wink)