How to properly input multiple formulas in one cell so that all formulas are executed

**roseyj** · 10-11-2016, 04:41 PM

How do you properly input multiple formulas in one cell so that all formulas are executed and one one text value is shown in the cell?

I am choosing basketball players to be on my fantasy team. I am the 4th person to pick each round between 10 people. Once a basketball player is chosen by my friends, I want a new option to populate in my cell to help me decide who to choose next.

With the help from another thread, I was able to get =IF(COUNTIF($C$2:$C$4,"WESTBROOK"),"HARDEN","WESTBROOK") to work, but I want to be able to do this with many many players and many different scenarios.

Does anyone know the best/easier way to do this? If I have to punch in every formula for each player contingency that is fine with me, but I can not get the multiple formulas to work in that one cell.

Here is the example of what I am doing wrong...

I input this formula into C5:

=IF((COUNTIF($C$2:$C$4,"WESTBROOK"),"HARDEN","WESTBROOK"), IF(COUNTIF($C$2:$C$4,"HARDEN"),"CURRY","HARDEN"), IF(COUNTIF($C$2:$C$4,"CURRY"),"DURANT","CURRY"))

what I want is if WESTBROOK is chosen by the first three guys, then my next choice will be HARDEN. If HARDEN is also chosen by the first three guys then my next option is CURRY, and if CURRY gets chosen then I will pick DURANT. I will add more later but I just needed help getting this to work. Thanks!!

please see picture of my spreadsheet:

Capture.JPG

**dflak** · 10-11-2016, 05:22 PM

So what you are saying is that you have the players ranked in the order you want to pick them, and want to pick the highest available player after those before you have already picked. Is this correct?

It would help a lot if you could provide us with a sample file. We'll need to see how the data are organized.

This is probably going to get done with a combination of a select change event, helper column, pivot table and named dynamic range.

I think it would also help if you tell us a bit about the selection process. Specifically, how to you get who the other participants picked. Do they just tell you and you make the entry?

I'll be on vacation for a week. If someone else doesn't pick it up in the meantime, I'll get to it when I get back.

**daffodil11** · 10-11-2016, 06:07 PM

I did this with a normal INDEX(..SMALL(IF, but added a NOT(COUNTIF( with as the range criteria to return only values of the index that haven't yet been found.

I already filled in the worksheet as you would. Just add your friends selections as they happen and the names you want will automatically update around them in your preferred order.

**roseyj** · 10-11-2016, 06:12 PM

Hi @dflak,

Your explanation is correct.
The data is organized based on a players ranking. Every round of picking (1stpick, 2nd pick, etc..) I have a set list of players to choose from based on their positions in a basketball game. I have a group of players I would like to pick first but if they get chosen before my turn, then I have to have a plan B or plan C or plan D.

The way that the participants select their players is that we will be holding a "Draft" where we get together and take turns choosing players based on our given order which was pre-selected randomly. I want to be able to fill out the names of each person's pick in the cells each round as they are picking, and then have an idea of who I would like to pick for myself. I will create the lists based on my research to know who I would pick next.

I got 4th so I will get to pick my first player for my team after the first 3 people (Chad, Kevin, Quincy). Once we get down to the 10th person, the choosing order reverses. The person that was 10th is also 11th, 9th is 12th, 8th is 13th.. and so on like a snake till everyone has full teams.

Also, if anyone has a better way of how I should set the spreadsheet up as well, I am open to any and all suggestions.

@daffodil11 I just saw your post as I was typing this out. I will give this a try and let you know if it works out. Thanks so much!

**dflak** · 10-19-2016, 02:45 PM

Here’s my shot at it.

There are three Excel tables and two pivot tables in this workbook.

The table in columns A:C is the Picks Table.

The table in columns E:G is the Player’s Table. I am sure you have some sort of ranking system. I used random numbers.

The table in column I is the People Table. It’s used to provide data validation for the column Who Picked Him.

Data Validation for the players gets tricky. If you select “Me” then the offset in column C is 3. Otherwise it’s 0.

I have a named range that overlays the pivot table in Column K called Their_Range. If one of them picks, then they can select from the names in Column K. If “Me” picks then you can select from the names in Column N. In the “final” version you can hide Column C.

As players are picked their availability goes from True to False, and this updates the pivot table. There is a change event on Sheet 1 that refreshes the pivot tables as players are picked.

So it works like this. Let’s say the picking order is Al, Ben, me, Carl, Darrin.

Select the Picker in Column A. In the example, Al is entered so we use the alphabetical list in column K for his dropdown. Al selects Player 01. Player 01’s availability is now False. The pivot tables refresh, and Player 01 is no longer available.

Ben selects Player 01 and his availability is now False.

Third man up is “Me”. Me selects from the list in Column N. The top player on that list is Player 05. That name is now dropped.

And so on.

Take a look at the formulas, name definitions and code. If you have any questions, ask!

How to properly input multiple formulas in one cell so that all formulas are executed

LinkBack

Thread Tools

Rate This Thread

Display

How to properly input multiple formulas in one cell so that all formulas are executed

Re: How to properly input multiple formulas in one cell so that all formulas are executed

Re: How to properly input multiple formulas in one cell so that all formulas are executed

Re: How to properly input multiple formulas in one cell so that all formulas are executed

Re: How to properly input multiple formulas in one cell so that all formulas are executed

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formulas lose reference after macro is executed

changin formulas based on one cell input

Excel 2011 Does Not Copy Cell Formulas Properly

Multiple formulas in selected cell without damaging previous formulas.

Excel 2007 : Formulas not working properly

User Input to change cell reference in formulas

[SOLVED] Formulas not calculating properly

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions