Newbie desperate for help!

[tuggers]

Hello all, i know there are loads of people who have asked questions on here regarding fantasy football, but most of them already seem to have a fair bit of knowledge about excel whereas im a complete beginner and really need help.
I currently run a fantasy football (soccer) league with a basic excel setup.

Firstly i would like to auto update player details from the required website, i have tried the web query process but unless im doing something wrong it didnt work!!?? The site is www.dreamteamfc.com on the right hand side of that page are links for player details in the form of abbreviations of there position i.e. GK, DEF etc if you click on these it will take you to the page i require the details from.

Any help would be truly appreciated

 

[sweater_vests_rock]

tuggers.

not sure what you tried before, but i set up a new query just fine. make sure you use the address to the pop-up table (the one that pops when you click on GK, D, etc) rather than the main page you provided the link for. click on the -> above the table (turning it to a check mark) and then complete the query.

here are the addresses i used (just copy and paste into the web query wizard).
for 'keepers:
http://www.dreamteamfc.com/dtfc05i/servlet/PostPlayerList?catidx=1&title=GOALKEEPERS&gameid=183

for defenders:
http://www.dreamteamfc.com/dtfc05i/servlet/PostPlayerList?catidx=2&title=DEFENDERS&gameid=183

for midfielders:
http://www.dreamteamfc.com/dtfc05i/servlet/PostPlayerList?catidx=3&title=MIDFIELDERS&gameid=183

for strikers:
http://www.dreamteamfc.com/dtfc05i/servlet/PostPlayerList?catidx=4&title=STRIKERS&gameid=183

to update the web queries, goto tools->refresh data

hope this helps, and best of luck in your league!
ben.

 

[tuggers]

thanks a million for your help, much appreciated, your a star.

But,

there is more id like to do in preparation for the new season.

next thing i require help with:

i would like to be able to provide a drop down list for all players in each position, showing players name and price, so users could select there players in this manner for there starting 11. I would also like the same thing for choice of substitutes when required. I would need for a user to be able to input there name and chosen team name and for all this info to be output to another page where all the created teams would be listed.
The selections made would be based on the price of each player so a calculation to check the user hasnt gone 'over budget' would need to be made before sending the data to another sheet.

Is this possible with excel?

 

[sweater_vests_rock]

hi tuggers.

i'm getting a little confused, so i wanted to make sure i have the context in which you're hoping to make these changes. what kind of fantasy league is this? do you draft players, or do you pick your team week to week? what are the position requirements (ex. 1 GK, 4 D, 4 M, 2 S) for the team?

additionally, let me repeat back what i heard from you:

so, first off you wanted to have tables of players by position automatically downloaded from the website. now you're hoping to taking these tables and create dropdown lists containing the player name and value.

once this is taken care of, it appears that you want to be able to have users create their own team from this data. i'm confused though -- how will they be able to pick multiple players by position? how will they be able to avoid picking the same team as someone else? are the dropdown lists used to generate the team, or just provide the data in a more usuable format for picking the team?

after the teams are picked, you then need to provide a logic check to ensure the team is under budget (and presumably a legal team in regards to positions).

i'm sure we can do all this in excel, but i think to get a satisfactory answer you're going to need to provide just a bit more info. hopefully my questions made sense?

cheers. ben.

 

[tuggers]

The fantasy league is for what us brits call football but everybody else calls soccer! lol

A team will be chosen at the start of the season but there will be an opportunity at pre defined times throughout the season to make transfers.

The positions generally consist of
1 x GK
4 x DEF
3 x MID
3 x STR

Yes, i want to take the players details table from the website and use that in excel to create a drop down list containing necessary info for people to make their team choices.
Im hoping it would be possible that the teams could be chosen by using the drop down list, there are a fairly large amount of choices for each position and it is possible for more than one person to choose the same players but i've never known it happen.
Then yes, it would require a check to confirm choices are within budget, a confirmation for anything else would be a bonus but not a real necessity.

If you look at www.dreamteamfc.com there are links there that suggest how to play and scoring for that league, this may help explain what im looking for?

By the way thanks for your interest and help, very much appreciated

Dave

 

[sweater_vests_rock]

tuggers.

i've put together a worksheet for you, but i think it would be a lot easier if i just sent it to you (involves lots of named ranges). PM me your email address and i'll get it to you asap.

cheers. ben.

 

[tuggers]

Hi all,

I have used the links as set out above for the web query, trouble is, every time i close the sheet down, when i open it again the web query link has reverted to the sites homepage rather than the players list links, how do i stop this from happening?

 

[sweater_vests_rock]

hi tuggers.

if the web queries are done through vba, make sure that this line is set to TRUE

.WebDisableRedirections = True

if the web queries are set-up manually, goto the website and select the table you wish as usual. before you select "import", however, select "options" from the top menubar. this will pull up a form with checkboxes. make sure that the box entitled "Disable Web Query Redirections" is checked.

sorry to have not caught this before, but i'm learning along with you
ben.

 

[tuggers]

Yet again, thanks for the advice mate, but its still not keeping the original settings after shutdown, even with the tickbox checked.
I dont know if it would make any difference if done through vba, but i only know how to do it manually.

 

[sweater_vests_rock]

tuggers.

i'm not sure how to fix this manually. great point about vba though; i'm pretty sure we could hard code this so that query always points to the correct address. i'll take a look as soon as i'm able.

cheers. ben.