Hello everyone!

I have created our volleyball league's 'schedule results' and 'standings' sheets in a Excel 2007 workbook (attached in present form).

What I need to do somehow dynamically pull the amount of 'Sets Played', 'Sets Won', 'Sets Lost', 'Points For', and 'Points Against' from the Schedule Results sheet into the Standings Sheet. I don't know what functions to use and what data manipulation needs to take place to accomplish this. I was hoping for some help from somehow much more experienced than I am in this sort of thing.

Thank-you so much for taking a look!

P.S. Bonus points for someone who can help me mass-produce the conditional formatting effect I have on the 'Schedule Results' sheet for clearly displaying the winning score of a set in bright green as I did in the first match. I'm not sure how to copy those rules to each of the 6 sets in the 42 matches in the sheet. That is 252 conditional rules if I do it my (slow) way.

2. ## Re: Creating Sports League Standings from Schedule Results Sheet - Please Help Put it Toge

I did not take a look at your first requirement. For conditional formatting you can try this.
Select B2: and apply these two rules in CF
=IF(AND(MOD(ROW(),4)=3,B2>B1),1,0) Formatting style green fill
=IF(AND(MOD(ROW(),4)=2,B2>B3),1,0) Formatting style green fill

Then copy the CF using format painter to other cells

3. ## Re: Creating Sports League Standings from Schedule Results Sheet - Please Help Put it Toge

Hi javacious,

I'm a volleyball nut, so I knew I had to help out...

Volleyball League v2.xlsx

A few things that you have to know...

I added a few helper columns in the Schedule Results tab, plus I named a few columns there... check out the Name Manager to see them all...

As for the conditional formatting... your formatting was fine, just copy these cells, then Paste Format to the other cells (as suggested from above)...

Let me know if this doesn't work for you or if you need some changes.

Take care,
Dennis

4. ## Re: Creating Sports League Standings from Schedule Results Sheet - Please Help Put it Toge

Originally Posted by Sindhus
I did not take a look at your first requirement. For conditional formatting you can try this.
Select B2: and apply these two rules in CF
=IF(AND(MOD(ROW(),4)=3,B2>B1),1,0) Formatting style green fill
=IF(AND(MOD(ROW(),4)=2,B2>B3),1,0) Formatting style green fill

Then copy the CF using format painter to other cells
Thank-you for taking the time to help Sindhus!

5. ## Re: Creating Sports League Standings from Schedule Results Sheet - Please Help Put it Toge

Originally Posted by djapigo
Hi javacious,

I'm a volleyball nut, so I knew I had to help out...

Attachment 191145

A few things that you have to know...

I added a few helper columns in the Schedule Results tab, plus I named a few columns there... check out the Name Manager to see them all...

As for the conditional formatting... your formatting was fine, just copy these cells, then Paste Format to the other cells (as suggested from above)...

Let me know if this doesn't work for you or if you need some changes.

Take care,
Dennis
Hey Dennis, thank-you so much - that is very cool to get help from another volleyball enthusiast. It's obviously a recreational league, but we wanted to be a little more organized this year.

Your code is actually perfect - it does everything I need it to! I had never used the name manager before so thanks for introducing me to that. I'm trying to learn more advanced Excel and so going through your code helped me learn a fair bit. There's a couple things I wasn't quite able to understand the logic of, but I almost got all of it.

I was going to upload the spreadsheet using Google Docs (Google Drive), but unfortunately, it doesn't convert all the formulas properly and none of the conditional formatting. I wanted to give each team captain the ability to log in and add/edit scores and then allow anyone in the league to view it using a public URL. So I figured out I could use Microsoft's SkyDrive to do the same thing. Not sure if you have any words of wisdom on the sharing/permissions of an Excel workbook file, but if you did, I'd be glad to hear them! I'll have to do more reading up on how that can work later today...

Thank-you both!

6. ## Re: Creating Sports League Standings from Schedule Results Sheet - Please Help Put it Toge

Hi javacious,

I'm myself only a rec player, but I try and play as often as I could... maybe around 10-15 hrs a week! and that's only on Fridays, Saturdays and Sundays... gotta love Los Angeles, we can play anytime...

Anyway, I'm glad the spreadsheet works for you... but as far as SkyDrive and sharing/permissions... that I'm not sure about...

Why don't you open a new thread and I'm sure there will be someone else that can advise you... besides, this forum suggests you start a new thread anyway with new topics...

Good luck! I'll be following your thread too, never hurts to learn something new...

PS Please make this thread SOLVED... go to the first entry, edit, go to advance and there should be a pull down that says solved...

There are currently 1 users browsing this thread. (0 members and 1 guests)