|
I'm really bored, and just playing around with Excel. My current goal is to make out a spreadsheet for the NHL, where I have every team listed. I have 82 columns wide, 1 for each game. At the beginning of the season, it just says their opponent, and I type in the result of the game for that team in the field: W, L, SOW, SOL, OTW, OTL I already have the code set up so that my standings page links to the "results" page, and the Wins, Losses, Overtime Losses, Points, ROW, and Games Played columns all update themselves. I don't know how to do macros. I did use the sort function to sort the divisions by points, highest first. Second criteria is games played, with lowest games played being at the top, and the 3rd criteria being ROW, with the highest. So it sorts out just like the standings are supposed to. Here is where I have some quesitons: 1) Is there a way to set it up to auto sort, so when I enter in a change in the "results" tab, it will automatically update the standings tab, without me having to manually go in and tell it to sort itself(it saves the sorting criteria, but I would like it to update any time a value is changed). 2) NHL is a bit different than other sports, in that each division winner is ranked 1/2/3 between the 3 division winners, then 4-8 for playoff spots are ranked by points between everyone else in the conference. I'm trying to have it rank each division based on the sorted criteria, so I can link from the divisions over to the conference standings I want to set up. I hope this all makes sense. Basically, auto sort and a way to rank using more than 1 criteria. I can't use just points as the rank because of the other criteria involved(Games Played and Regulation/Overtime wins).
|
#
¿
Mar 17, 2012 01:23
|
|
|
|
| # ¿ Apr 25, 2024 07:49 |
|
|
Sub Par posted:A bunch of excel code I didn't want to quote to take up a bunch of page space. Wow, you really know your stuff. I was hoping there was a way I could set it up to do it without using macro codes, like a function I could type in that would be able to do it. I really don't want to be using something that I don't know how to code, but it works. Testing it up, changing my results tab, if a team would be passing up another team based on points, it automatically sorts it in the standings tab. Couple questions: 1) I'm doing each division seperately, can I use that code multiple times, where it will change according to the specified fields? Like, I have the results tab, with every team, or would I have to make 2 different sheets for each division? No big deal if I would have to do that, would just be easier to have them all on 1 tab. 2) Is there a way to rank a list according to multiple criteria? I know there is a rank that if there is a tie(If you go by points, let's say 2 teams have 70 points each), it will come back with the same rank. I ask this because of the way standings are formulated with the NHL. I'm trying to rank the 3 division leaders, to place into the top 1-3 spots, then fill in the rest with the remaining teams. I figure a ranking system would be easiest to pull the data? 3) Maybe I'm doing it the wrong way, and should just try to make the conference standings without the division standings? But is there a way to copy over an entire row of data(Like A1:A5), and pull it into another table. For example: Pacific Division standings, 4 teams. The top team would be either 1,2,3 in the conference. Then of the other 3 teams, they are below 1,2,3. I want to be able to have the conference standings seperate of the division standings, and if possible for all of them to update just by me entering in the Win/Loss information in the results tab. My whole goal out of all of this is just to be able to toy around with possible scenarios, if x team wins what happens, etc. Extra stats, and a bunch of other "worthless" information that you have to do research for.
|
|
#
¿
Mar 17, 2012 20:58
|
|
|
Sub Par, and anyone else. Here is a link to the excel file I have been working on. http://www.mediafire.com/?73uau3rcqn72fc8 I have the Pacific Division lined up with the results tab to auto update, thanks to Sub Par's code, it sorts automatically. I just typed in the numbers manually for the other division's. I then just copied the cells over from the divisions to the conference. I guess I need to add the $ sign for each cell from the results tab so that the numbers are showing correctly(the Pacific shows 0's since the cells referenced changed in the results tab). Or I could always do =A2,=B2, etc. for the Conference standing, and then use the sort function to sort it accordingly? Hope this makes sense, I just have to make the divisions, then sort out which team gets the #1-#3 seed(each division leader sorted accordingly), then fill in the rest.
|
|
#
¿
Mar 17, 2012 21:43
|
|
|
BTW, if it's just functions that I can enter to do something, just point me in the right direction and I can toy with it to figure it out. I want to be able to do this on my own. I just wish I was more knowledgable with all of this.
|
|
#
¿
Mar 17, 2012 21:57
|
|
|
Edit: I found the power of the record macro function. I now have 2 different macros sorting 2 different divisions on the same worksheet. I think I can figure this out slowly but surely!
Bulls Hit fucked around with this message at 23:02 on Mar 17, 2012 |
|
#
¿
Mar 17, 2012 22:53
|
|
|
Is there a way, when you update your excel spreadsheet, to have it upload to a table you uploaded to the web and update that table? Or do you always have to re-upload the spreadsheet data?
|
|
#
¿
Mar 23, 2012 01:57
|
|
|
Edit: Figured it out. If function ftw!
Bulls Hit fucked around with this message at 02:23 on Apr 5, 2012 |
|
#
¿
Apr 5, 2012 02:18
|
|
|
I doubt this is possible, but figured I would ask as someone might know. I'm trying to build a spreadsheet to budget per month what I take in and what I expense out. Right now I post my paycheck each time I receive one, and I post up what dates and the amount of when my expenses would be charged to my bank account for auto payments. I'm wondering if it was at all possible to pull the date/amount due from the sites automatically when they update so I can see without having to login each time?
|
|
#
¿
Jul 13, 2012 18:03
|
|
|
G-Dub posted:You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls. Unfortunately I'm a beginner, so this might be too much trouble than it's actually worth. There is no problem checking all the time what the due date is and such, but I figured this would just be easier.
|
|
#
¿
Jul 15, 2012 00:20
|
|
|
|
| # ¿ Apr 25, 2024 07:49 |
|
|
G-Dub posted:You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls. I did a search for Microsoft Internet Controls, and can't really find exactly what to do. Is there a tutorial page that might walk me through what to do that you know of?
|
|
#
¿
Jul 16, 2012 17:27
|
|