+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-10-2016
    Location
    Houston, Texas, US
    MS-Off Ver
    Office 2010
    Posts
    6

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

    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

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,104

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

    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.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    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.
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Registered User
    Join Date
    10-10-2016
    Location
    Houston, Texas, US
    MS-Off Ver
    Office 2010
    Posts
    6

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

    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!

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,104

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

    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!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formulas lose reference after macro is executed
    By bgcm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2015, 01:35 PM
  2. changin formulas based on one cell input
    By amartino44 in forum Excel General
    Replies: 3
    Last Post: 12-07-2013, 02:23 PM
  3. Excel 2011 Does Not Copy Cell Formulas Properly
    By fender128 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-05-2011, 12:44 PM
  4. Multiple formulas in selected cell without damaging previous formulas.
    By excel5111987 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-05-2011, 06:15 AM
  5. Excel 2007 : Formulas not working properly
    By azalner in forum Excel General
    Replies: 4
    Last Post: 07-30-2010, 06:22 PM
  6. User Input to change cell reference in formulas
    By williampdx in forum Excel General
    Replies: 5
    Last Post: 03-31-2010, 02:04 PM
  7. [SOLVED] Formulas not calculating properly
    By SueK in forum Excel General
    Replies: 2
    Last Post: 05-10-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1