Anybody any good w/Excel? Needing an auction draft spreadsheet (1 Viewer)

[T J]

I had one our league used but my hard drive crashed and it was not backed up and now our draft is Saturday.

I'd be willing to pay someone $20 if they could come up with something that worked for us.

Basically, what I need is this:

We have 14 teams with 16 man rosters with $200 to auction with. I'm in need of a spreadsheet we can enter each team's selections on with a column for position, NFL team the player is on, the name obviously, but moreso, the amount the player was won for. And then here's where the need for spreadsheet help comes in - you must have at least $1 for each remaining roster spot yet to be filled so we need a running total of what each team's maximum bid is assuming they need to have at least $1 left for each remaining roster spot.

In year's past, there was a spreadsheet with 14 tabs where I would input each owner's name for easy reference and each tab would have the info I indicated above, but also there was a master page that every owner's money spent and max bid was shown. The tabs I use to inout the rosters and the master page is one we actively refer to during the draft to see where each owner stands on their max bids.

I have no clue how to do this. Can anyone help?

Thanks!

Russ

 

[belljr]

Columns B2 -B17 are the amount entered for bids

=200-SUM(B2:B17)-(15-COUNTIF(B2:B17,">0"))

 

[Spike]

If what belljr posted doens't work, Wilked is excellent with all matters pertaining to Excel.

 

[T J]

Columns B2 -B17 are the amount entered for bids

=200-SUM(B2:B17)-(15-COUNTIF(B2:B17,">0"))

I think this works.

Thanks!

One more question though. I used the formula on the individual team page - no problem, and on the master page - no problem. So that's functional and I can work with that, but any idea how I can populate the master page from the data input on the indivual team pages rather than entering the amounts on both the individual and the master page separately?

 

[Bull Dozier]

Columns B2 -B17 are the amount entered for bids

=200-SUM(B2:B17)-(15-COUNTIF(B2:B17,">0"))

I think this works.

Thanks!

One more question though. I used the formula on the individual team page - no problem, and on the master page - no problem. So that's functional and I can work with that, but any idea how I can populate the master page from the data input on the indivual team pages rather than entering the amounts on both the individual and the master page separately?

Link the cells on the individual team page to the master page?

 

[belljr]

=SheetName!Cell

So for example on Sheet1 you have cell A1 that contains 150

On Sheet2 you want the same exact information?

So sheet2 cell B1 for example would have this:

=Sheet1!A1 this will show 150

So the contents in cell B1 on sheet 2 will equal that of Sheet1 cell A1

 

[Judge Smails]

Man - I can use you guys for some sales reports...

 

[Apple Jack]

Still the best software application ever developed. Sorry Lotus.

 

[hagmania]

Man - I can use you guys for some sales reports...

Excel's not that tough, given you have some time and a fair bit of Internet search skills.

 

[Buckfast 1]

I have an Excel template that already contains all of the formulas you should need. You may just need to change them slightly to account for more teams. Let me know if you want me to email it over to you.

 

[Dickie Dunn]

I use Excel a lot and still feel like I only use about 10 percent of its "power", if that. Most of it I've learned by looking at other spreadsheets, taking them apart and seeing what they do.

Visual Basic is a whole new animal that I have barely scratched (other than recording macros ... highly recommended for any cumbersome tasks that you do on a regular basis).

 

[T J]

I have an Excel template that already contains all of the formulas you should need. You may just need to change them slightly to account for more teams. Let me know if you want me to email it over to you.

I would actually. It may well be better than what I've put together. I'm PMing you my email. Thanks!

 

[T J]

I have an Excel template that already contains all of the formulas you should need. You may just need to change them slightly to account for more teams. Let me know if you want me to email it over to you.

I would actually. It may well be better than what I've put together. I'm PMing you my email. Thanks!

Well, I tried to PM you but it says you can't receive any messages so I couldn't.

 

[T J]

e

=SheetName!Cell

So for example on Sheet1 you have cell A1 that contains 150

On Sheet2 you want the same exact information?

So sheet2 cell B1 for example would have this:

=Sheet1!A1 this will show 150

So the contents in cell B1 on sheet 2 will equal that of Sheet1 cell A1

I have 14 individual sheets with what will be 16 unique numbers (winning bids) on each individual sheet. To link this info to the master page, would I have to link each indvidual cell to the corresponding place on the master? 'cause that would be 224 individual cells I'd have to link. Sorry - excel novice here - just asking.

 

[Dickie Dunn]

No, just SUM the total of the individual winning bids on each team page, and then use that as the number you link to the Master page.

Or better yet, put the formula posted above on each of the individual team pages, and then link those cells to the Master page.

 

[Psychopav]

I'm curious: if you're willing to pay someone $20 for this, why wouldn't you just pay the extra $10 and get a subscription to Footballguys and use the Draft Dominator to run and summarize your draft?

 

[rodg12]

e

=SheetName!Cell

So for example on Sheet1 you have cell A1 that contains 150

On Sheet2 you want the same exact information?

So sheet2 cell B1 for example would have this:

=Sheet1!A1 this will show 150

So the contents in cell B1 on sheet 2 will equal that of Sheet1 cell A1

I have 14 individual sheets with what will be 16 unique numbers (winning bids) on each individual sheet. To link this info to the master page, would I have to link each indvidual cell to the corresponding place on the master? 'cause that would be 224 individual cells I'd have to link. Sorry - excel novice here - just asking.

On the master sheet, you should be able to fill in the first linked cell for each team and then fill down for the remaining cells for that team. That way, you'll only have to manually link 14 cells.

 

[Buckfast 1]

I have an Excel template that already contains all of the formulas you should need. You may just need to change them slightly to account for more teams. Let me know if you want me to email it over to you.

I would actually. It may well be better than what I've put together. I'm PMing you my email. Thanks!

Well, I tried to PM you but it says you can't receive any messages so I couldn't.

I cleared out my PM inbox if you want to try again. Sorry about that.

 

[T J]

I'm curious: if you're willing to pay someone $20 for this, why wouldn't you just pay the extra $10 and get a subscription to Footballguys and use the Draft Dominator to run and summarize your draft?

I commish the league and these are tools we use live during the draft.