American Football Suicide Pool

P!tt

New Member
Joined
Oct 3, 2006
Messages
3
OK, I swear I have searched up and down for all words nd phrases I can think of but I can't find how to do this. I am not and Excel expert in any fashion. But I think that what I am trying to do is fairly simple or at least would be for the experts here in this forum.

I would like to have a drop down list in every cell. The list is dynamic to the point that as each previous cell in the row is selected, the list removes that selection. So if the list has Apple, Orange, Banana, Lemon, Lime in it and for the first selection in the row (C2) Lemon is selected from the dropdown, then in (C3) the only choices are Apple, Orange, Banana, Lime.

Does that make sense? So it is for a Football pool that is a single elimination pool but you can't choose the same team twice. Each player obviously has their own set of teams but the team has to be removed from the list each week.

I searched for many iterations of how to describe this but came up with nothing similar to text conditional list removal per row. :(

I appreciate the site and the ability to be able to ask and search for these kinds of things online. How exactly did we get through life before the internet again?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!

"I would like to have a drop down list in every cell. "

With 16,777,216 cells on a single sheet, I think you're exaggerating!

Post back with info on:

1) how your sheet is set up & where exactly this data is to be entered
2) where the master list of teams is held
3) anything else your think might be relevant
 

P!tt

New Member
Joined
Oct 3, 2006
Messages
3
Very frustrating that everything anyone is looking for is probably someplace on the web but finding it is the challenge. Not knowing enough about Excel to know what to call what I'm looking for was the problem. Thank you for the link. I believe with my limited programming background I can modify this info to fit my needs.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"I believe with my limited programming background I can modify this info to fit my needs."

& if you can't, post back with the details...
 

P!tt

New Member
Joined
Oct 3, 2006
Messages
3
OK, I got it to work for the first "player". Here's the layout:
ColA contains all players in the pool. Row1 contains all the weeks of the football season. So I modified the code on the link that Brian gave and got it changed from a column of data validation to a row of validation. It correctly reads the row 3 starting at C and ending at R and figures out which teams have already been picked by Player 1. The problem is that nuless there is some way to specify an array of cells for the formula to read from, I think I would have to do that procedure of creating a Validation list and an Array list for each player. There are 40+ of them.

I tried applying the formula to the entire range (which is shortened because the year has already gone four weeks into it) from G3 to R32 (some of the 40+ players are already out because they picked a losing team) but it is using the formula calculated off Row 3. Here is the formula I am using: =IF(COUNTIF(Suicide!$C$3:$R$3,A1)>=1,"",ROW()) That is applied on a different worksheet titled Teams that has the array formula as well. It is this: =IF(ROW(A1:A32)-ROW(A1)+1>COUNT(B1:B32),"",INDEX(A:A,SMALL(B1:B32,ROW(INDIRECT("1:"&ROWS(A1:A32)))))).

This is getting long and complicated which you guys probably love but gives me a headache. Anyway, it would appear that if the values for $C$3:$R$3 could change based on something, dynamically, then it would work for all Players without having to do each player as a separate validation list. I mean after all the list of teams is static and the list of choices per player is restricted to the list of teams.

I'm close but I don't know the syntax of what I'm looking for. Any thoughts?
 

Forum statistics

Threads
1,136,878
Messages
5,678,302
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top