Excel spreadsheets
Excel spreadsheets
I'm not sure where to post this (but considering it's regarding MB material on their spreadsheets I thought I'd post it here?), but say I have three spreadsheets.
Spreadsheet A has a list of projected stats (and let's just say they are from MB )
Spreadsheet B has a list of projected stats that the players will be organized by, during my draft
Spreadsheet C is created specifically for my league. On the tab for Free Agents, is there a way to link certain cells into the third?
There is a part two, but I'll worry about that when I get there.
Thanks guys!
(I've added in my the points from my league to give point values to the players so I can see which range they fall under outside of just their $ value.)
Spreadsheet A has a list of projected stats (and let's just say they are from MB )
Spreadsheet B has a list of projected stats that the players will be organized by, during my draft
Spreadsheet C is created specifically for my league. On the tab for Free Agents, is there a way to link certain cells into the third?
There is a part two, but I'll worry about that when I get there.
Thanks guys!
(I've added in my the points from my league to give point values to the players so I can see which range they fall under outside of just their $ value.)
Re: Excel spreadsheets
While I am sure the answer is yes, I don't understand exactly what you are trying to accomplish. Please either PM with with more specifics, or even better e-mail me the sheets with a description of what you want to do
todd@mastersball.com
todd@mastersball.com
Catchers are like prostate exams -- comes a time where you can't put if off any longer, so you may as well get it over with and take it up the butt - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
- viper
- Hall of Famer
- Posts: 1480
- Joined: December 31st, 2008, 11:32 pm
- Preferred Style: Currently in an AL-only league with the Bill James Technical RCA as the single hitting category and ERA as the single pitching category.
- Contact:
Re: Excel spreadsheets
When working for Marriott Corporation back in the days when the IBM PC just came out, I told a VP that if you can explain it to me, I can do it. He never proved me wrong and we had a wonderful working relationship. My caveat was that there were many thing that could not be explained and computers/programs/spreadsheets required firm rules to work by. My second sidenote was don't assume what is easy and what is hard. Some things that seem impossible are very easy for computers and some things that seem very easy are not the same with inflexible programs. He dealt mostly is the world of complex "what if" issues. Those are like falling off a log for a computer.
Bottom line is that I bet on Todd & Gary being able to help
Bottom line is that I bet on Todd & Gary being able to help
The avalanche has started. It is too late for the pebbles to vote. -- Ambassador Kosh
Mike Ladd
Buffy, the Umpire Slayer
Mike Ladd
Buffy, the Umpire Slayer
Re: Excel spreadsheets
I'll have to toy with that. It looks close to what I'm trying to do, just have to know how to link different files together rather then different tabs or different info on the same tab.GaryJ wrote:vlookup is going to be your friend.
Tood, I just send you a PM on it.
Thanks guys!!
Re: Excel spreadsheets
As I mentioned in the PM, the names for all the sheets have to be exactly the same and vlookup will do the trick.
The problem is getting the names the same, though there are tricks for that too.
The problem is getting the names the same, though there are tricks for that too.
Catchers are like prostate exams -- comes a time where you can't put if off any longer, so you may as well get it over with and take it up the butt - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
Re: Excel spreadsheets
Two tips:
1) When using VLookup always set the last parameter FALSE. That way, it will require an exact match and give you an error on names that don't match. Also, you don't have to sort the list first then. (if you then sort by the vlookup column though all the errors will sort together and you can quickly find the names you need to lookup)
2) Move all the sheets you are working with into one workbook. Right-Click the tab itself and select Move or Copy. This just makes your life a whole lot easier.
Bonus) When you go to your draft/auction and you want all those values to stay static, highlight the column hit Ctrl+C (copy) and then Edit > Paste Special and selet the button for "Values". That will get rid of the formulas and leave the values instead. At a draft, it's a lot faster, but you might not want to do it earlier when your projections might change.
Bonus #2) If you wrote a vlookup similar to
=VLOOKUP(A6, Hitters_02_01!A6:Y506, 9,FALSE)
because you maybe wanted the HRs for your player, as you copy down the list you might find some errors coming up. The problem is, if you fill down, the row numbers all increment, and if you fill right the column letters increment, so the next row down would look like:
=VLOOKUP(A7, Hitters_02_01!A7:Y507, 9,FALSE),
or the next row right would look like
=VLOOKUP(B6, Hitters_02_01!B6:Z506, 9,FALSE).
To fix this, add a $ before any values that won't change along the way. In this case, your range's rows and columns, and the lookup field's column (but not row):
=VLOOKUP($A6, $A$6:$Y$506, 9,FALSE)
Do you get the feeling I might have a ridiculously complex system of lookups and excel spreadsheets going?
1) When using VLookup always set the last parameter FALSE. That way, it will require an exact match and give you an error on names that don't match. Also, you don't have to sort the list first then. (if you then sort by the vlookup column though all the errors will sort together and you can quickly find the names you need to lookup)
2) Move all the sheets you are working with into one workbook. Right-Click the tab itself and select Move or Copy. This just makes your life a whole lot easier.
Bonus) When you go to your draft/auction and you want all those values to stay static, highlight the column hit Ctrl+C (copy) and then Edit > Paste Special and selet the button for "Values". That will get rid of the formulas and leave the values instead. At a draft, it's a lot faster, but you might not want to do it earlier when your projections might change.
Bonus #2) If you wrote a vlookup similar to
=VLOOKUP(A6, Hitters_02_01!A6:Y506, 9,FALSE)
because you maybe wanted the HRs for your player, as you copy down the list you might find some errors coming up. The problem is, if you fill down, the row numbers all increment, and if you fill right the column letters increment, so the next row down would look like:
=VLOOKUP(A7, Hitters_02_01!A7:Y507, 9,FALSE),
or the next row right would look like
=VLOOKUP(B6, Hitters_02_01!B6:Z506, 9,FALSE).
To fix this, add a $ before any values that won't change along the way. In this case, your range's rows and columns, and the lookup field's column (but not row):
=VLOOKUP($A6, $A$6:$Y$506, 9,FALSE)
Do you get the feeling I might have a ridiculously complex system of lookups and excel spreadsheets going?
Re: Excel spreadsheets
Welcome back Homertime, the tool formerly known as PAUL and I both missed you
Catchers are like prostate exams -- comes a time where you can't put if off any longer, so you may as well get it over with and take it up the butt - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
Re: Excel spreadsheets
Thanks. If there's anything it needs, feel free to let me know. I'm willing to help where I can.
Re: Excel spreadsheets
Thanks Homer, I found a few youtube videos that showed some of this stuff after Gary mentioned it, but it still helps!Homertime wrote:Do you get the feeling I might have a ridiculously complex system of lookups and excel spreadsheets going?
And I hope my spreadsheet one day becomes similar to yours.
-
- Major League Veteran
- Posts: 203
- Joined: January 2nd, 2009, 10:37 am
- Preferred Style: 5x5 keeper, auction
- Location: Ithaca, NY
Re: Excel spreadsheets
On draft day I need to supply the active rosters of the MLB teams in an AL league and an NL league. I took the Orioles roster just to see how difficult it would be. In column A, I have a player number, the first name, and the last name in that order. Is there a way to ditch the numbers and sort by last name? (I leave out the positions of pitcher, infielder, and outfielder separating the players; perhaps there is a search ad replace with nothing function for that; a search and replace function for the numbers?).
I have gotten the active rosters from the team sites on MLB.com. If there is an alternative source that would not lead to these difficulties, I would be happy to know about that.
I have gotten the active rosters from the team sites on MLB.com. If there is an alternative source that would not lead to these difficulties, I would be happy to know about that.
Re: Excel spreadsheets
Is the data presented as
Number, Space, First Name, Space, Last Name
??
If so, use Data - Text to Colums, delimited, click on "space", then click finish. You should now have something in columns A, B, C.
Delete column A (the #'s), then do (assuming this is in row 1 of your spreadsheet and now the first name is in column A and last name in column B) in column C
CONCATENATE (B1, ",", A1)
This should show the players as Last Name,First Name. If you want a space after the comma, it's
CONCATENATE (B1, ",", " ", A1)
Then it will show as Last Name, First Name
You can then sort by Last name
Let me know how it goes.
Number, Space, First Name, Space, Last Name
??
If so, use Data - Text to Colums, delimited, click on "space", then click finish. You should now have something in columns A, B, C.
Delete column A (the #'s), then do (assuming this is in row 1 of your spreadsheet and now the first name is in column A and last name in column B) in column C
CONCATENATE (B1, ",", A1)
This should show the players as Last Name,First Name. If you want a space after the comma, it's
CONCATENATE (B1, ",", " ", A1)
Then it will show as Last Name, First Name
You can then sort by Last name
Let me know how it goes.
- viper
- Hall of Famer
- Posts: 1480
- Joined: December 31st, 2008, 11:32 pm
- Preferred Style: Currently in an AL-only league with the Bill James Technical RCA as the single hitting category and ERA as the single pitching category.
- Contact:
Re: Excel spreadsheets
since I cannot spell con-cat-in-eight, I would do +B1&", "&A1
then copy the whole column to notepad
then copy the notepad version right over the place it came from.
This makes it something like "Zola, Todd" and it is a text entry without any references to anything
then copy the whole column to notepad
then copy the notepad version right over the place it came from.
This makes it something like "Zola, Todd" and it is a text entry without any references to anything
The avalanche has started. It is too late for the pebbles to vote. -- Ambassador Kosh
Mike Ladd
Buffy, the Umpire Slayer
Mike Ladd
Buffy, the Umpire Slayer
Re: Excel spreadsheets
You're not the only person to tell me to use that & function.
I refuse to learn, it seems. Your way is probably simpler.
I refuse to learn, it seems. Your way is probably simpler.
Re: Excel spreadsheets
You can leave out the notepad step by copying the cell with the &", "& and paste special, values
right clicking will get you to the paste special option
right clicking will get you to the paste special option
Catchers are like prostate exams -- comes a time where you can't put if off any longer, so you may as well get it over with and take it up the butt - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
-
- Major League Veteran
- Posts: 203
- Joined: January 2nd, 2009, 10:37 am
- Preferred Style: 5x5 keeper, auction
- Location: Ithaca, NY
Re: Excel spreadsheets
Thanks so much. After I got the material into three columns, I used a different method, but it was all downhill.
-
- Hall of Famer
- Posts: 1574
- Joined: January 3rd, 2009, 12:09 am
Re: Excel spreadsheets
Don't know if this is possible (or too time consuming) but there were several Excel tips on the old board that may be worth extracting to here, oh forum funklord. Especially by user Springfield.
These are my views based on my own opinions and observations - your mileage may vary.
"KNOW THY LEAGUE" - the Forum Funklord - 4/13/2009
Fantasy is managing stats ... roto is managing teams
"KNOW THY LEAGUE" - the Forum Funklord - 4/13/2009
Fantasy is managing stats ... roto is managing teams
-
- Major League Veteran
- Posts: 203
- Joined: January 2nd, 2009, 10:37 am
- Preferred Style: 5x5 keeper, auction
- Location: Ithaca, NY
Re: Excel spreadsheets
Another question. If you copy and paste a team roster into microsoft excel on a pc, it will show up in row A apparently in one cell across the spreadsheet. (On a mac, it will show up in column A down the spread sheet). Is there a way to avoid this or to convert the row into a column or set of columns.
I did try to save it in word first. The most recent word saves it across the page and stops at the margin going to the next line. If you hit return for each player and save the document in text and then copy, you can then paste it into excel where it will show up in columns. But that requires you to hit return for each player and if you are doing it for an entire league you begin to think there must be an easier way for pc users.
(For those of us with access to a mac, we can do the work there and then mail it to the pc if we need it on a pc.)
I did try to save it in word first. The most recent word saves it across the page and stops at the margin going to the next line. If you hit return for each player and save the document in text and then copy, you can then paste it into excel where it will show up in columns. But that requires you to hit return for each player and if you are doing it for an entire league you begin to think there must be an easier way for pc users.
(For those of us with access to a mac, we can do the work there and then mail it to the pc if we need it on a pc.)
Re: Excel spreadsheets
There is probably a better answer for this, from someone who knows "why", but when this happens, try using paste special - text instead of just paste. You can get paste special by right clicking, then I believe text is the 3rd option.
Catchers are like prostate exams -- comes a time where you can't put if off any longer, so you may as well get it over with and take it up the butt - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
I'd rather be wrong for the right reasons than right for the wrong reasons - The Forum Funklord
Always remember, never forget, never say always or never. - The Forum Funklord
You know you have to seek therapy when you see one of your pitchers had a bad night and it takes you 15 minutes to find the team you have him on. - The Forum Funklord
Re: Excel spreadsheets
Thats why I love this place...Not only do you get baseball analysis, but you get Excel and Microsoft help as well....
-
- Major League Veteran
- Posts: 203
- Joined: January 2nd, 2009, 10:37 am
- Preferred Style: 5x5 keeper, auction
- Location: Ithaca, NY
Re: Excel spreadsheets
Todd
Thanks. It worked. You're a star.
Thanks. It worked. You're a star.
-
- Hall of Famer
- Posts: 1574
- Joined: January 3rd, 2009, 12:09 am
Re: Excel spreadsheets
It is most likely that the source is using delimitted text (uses separators such as tabs, spaces, etc.) in between fields. Once you have done what Todd suggested, click and drag along the first (and only the first) column of data. Select the Data drop down window, then select Text to Columns. A window will pop up asking if the text is either fixed width or delimited. Click the Delimited radio button, then press NextTodd Zola wrote:There is probably a better answer for this, from someone who knows "why", but when this happens, try using paste special - text instead of just paste. You can get paste special by right clicking, then I believe text is the 3rd option.
The next window will give you an option of delimiters and how the separation process will look [depending on the types of delimiters you chose]. When you are satisfied, click on Next, then Finish in the final window. The text should now be separated into distinct columns.
These are my views based on my own opinions and observations - your mileage may vary.
"KNOW THY LEAGUE" - the Forum Funklord - 4/13/2009
Fantasy is managing stats ... roto is managing teams
"KNOW THY LEAGUE" - the Forum Funklord - 4/13/2009
Fantasy is managing stats ... roto is managing teams
Re: Excel spreadsheets
You know one day I had a lady email me and she said "my xcell and worde things aren't working, I need help" I responsed, Yes you do!