+ Reply to Thread
Results 1 to 22 of 22

Drop Down List Multiple Outcome Formula

  1. #1
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Drop Down List Multiple Outcome Formula

    Hello,

    I have 3 drop down lists on my "Summary" page. Each list is titled Drop Down List 1, 2, & 3.

    Drop Down List 1
    Cell A5 = Drop Down list of basketball team names.
    Cell B5 = Which question (category) I'm trying to answer
    Cell C5 = The answer to the options chosen in B5

    Example: If i chose Alabama for Cell A5 & "Tourney App." for cell B5 then the answer that should be displayed in Cell C5 is the number 15. That number comes from the Yellow cell (Cell B7) of the "Seed Stats" page. The "Seed Stats" page is the page that will be used to answer all three questions in the Drop Down List of cell B5 on the "Summary" page.

    Say i switch cell B5 on "Summary" to Overall Win/Loss Record than the answer in cell C5 will be 11 - 4 (with the format of 11_-_4). That answer is derived from cells C7 & D7 of the "Seed Stats" page.

    And if i switch cell B5 on the "Summary" to Overall Win % than the answer in cell C5 will be 73% which comes from cell E7 of the "Seed Stats" page. The process would repeat itself for the any team that is chose for cell A5 and any category chosen in cell B5 of the "Summary" page.

    What formula do i use for cell C5 to get the appropriate answers?

    *Instead of making this post extra long to explain the other 2 drop down lists I'll wait until i solve the first drop down list before i continue on. Thanks in Advance!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Hi,

    Just to get this started off, I added a dropdown list for the team name in A5, go to data validation and change it to 'List', set the source as...

    ='Seed Stats'!$A$4:$A$354

    Then in A5 you can lookup the data with...

    =INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),3)

    If you want to make B5 a dropdown list as well to lookup from the column headers on the Seed Stats sheet, then the fact that those column headers are merged cells with multiple columns below them is not ideal for doing a table lookup. You may have to adjust the layout.
    Attached Files Attached Files
    Last edited by D.Lovell; 05-04-2017 at 05:56 PM.

  3. #3
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    I'm sorry, but that document you uploaded does not work correctly. It will not display every teams tourney app let alone the other categories.

    Take for example: If you chose "Belmont" in A5, cell C5 is blank. I'm thinking the #3 in the formula should be a 2.

    It's going to be difficult to adjust the format because it's specific for that page.
    Last edited by RachelMads02; 05-04-2017 at 06:36 PM.

  4. #4
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    I've gotten this so far:

    =IF(B5="Tourney App.",INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),2),IF(B5="Overall Win/Loss Record",INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),3)&" - "&INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),4)))

    That provides me with the first two answers (Tourney App. & Overall Win/Loss Record), but i don't know how to add the last answer (Overall Win %).

  5. #5
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Yes sorry, I put the wrong column number for the index function, it should have been column 2 for what I was saying, not 3.

    Basically this lookup problem is made overly complicated by the design layout, the problems are....

    1. You are looking up from table headers that contain merged cells, and the actual data you want is in multiple columns below that in some cases.

    2. Not all table header look ups exist on the same row.

    3. The output values are 3 completely different data formats.

    Have a look at what I have done to this now, I unmerged the cells for the table headers, you could put loads of spaces in front to get them to the middle for the purpose of the appearance. I added the 'Overll Wn %' column header in E2, and 'Tourney App.' in B2 on the Seed Stats sheet, so that all the drop down list lookup values exist on the same row. I made B2 on the Summary sheet into a dropdown list using your range E4-E6 on that sheet.

    Then accounted for the different formats to return data in, by using this rather long formula in C5 on the summary sheet.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Possibly this should be wrapped in an IFEROR statement, like this....

    =IFERROR(IF($B$5=$E$4,INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0)),IF($B$5=$E$5,""""&INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0))&" - "&OFFSET(INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0)),0,1)&"""",IF($B$5=$E$6,ROUND(INDEX(Table19[[Team]:[Win %]],MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0))*100,0)&"%"))),"")

    Still not perfect though but getting somewhere at least I think

  7. #7
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    How do i remove the " " around the win/loss record answer? The section in the E:F is just an example of the answers that are found on the "Seed Stats" page. I can't make anything in cell C5 = that range.
    Last edited by RachelMads02; 05-04-2017 at 06:52 PM.

  8. #8
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Take out the &"""" , """""&, so write it like this......

    =IFERROR(IF($B$5=$E$4,INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0)),IF($B$5=$E$5,INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0))&" - "&OFFSET(INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0)),0,1),IF($B$5=$E$6,ROUND(INDEX(Table19[[Team]:[Win %]],MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0))*100,0)&"%"))),"")

    Also if you do decide to add spaces in front of text in the column headers to central the text above multiple columns, then it may be necessary to use the TRIM function as well to account for that.

  9. #9
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    I will continue to help as much as I can, it's all about understanding exactly what you want really and how best to go about it.

    Do you understand what I mean about the table lookup? Ideally there needs to be 1 row on the lookup sheet that has all the same things which exist in the dropdown list.
    Last edited by D.Lovell; 05-04-2017 at 07:15 PM.

  10. #10
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    I think I'm following correctly.

    In this example does the lookup table = E4:E16 on the "Summary" page? If so can i put that table on a different page in this workbook rather than on the "Summary" page?

  11. #11
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Every time Summary or 'Seed Stats' appears in the formula, it refers to the cell or range defined just after it on that sheet, you can change that in the formula to work for a sheet with a different name, as long as the layout is the same, remember I have made some changes to the layout on this example to allow this to work.

    Also....

    The dropdown list in B5 on the summary sheet is a list of the data in E4-E6 on the same sheet. To make a dropdown list, there has to be a list of the stuff you want in it on a sheet somewhere within the workbook.

    Ideally you make the dropdown lists from the actual column headers and row labels of the lookup table. But you can just make a list of them somewhere else and use that instead, which is what is done here, just make sure they are all exact matches of the real lookup values. I have used your example data in E4-E6 for that dropdown list.

    I have only looked properly at the top example, not the rest, so E4:E6 so far, not E4:E16. There is still some tidying up to do here before I would do the rest of them.....

    Question:

    Regarding how to display the data from range E:F, it is 2 separate values so one has to decide what we do with them. I have done it as shown on your example, with both separate values displayed with spaces and a dash between them, is this what you want?

    I would be interested to know how you would like this to look where 1 of the 2 columns contains a value, while the other does not (this is in your example). The result from my formula as it stands looks untidy where this is the case. We could display just the one value that is there, but it wouldn't be apparent from this which of the 2 columns it had come from (wins or losses). The value could be labeled W or L based on which column it came from for example.
    Last edited by D.Lovell; 05-04-2017 at 08:12 PM.

  12. #12
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    To make a dropdown list, there has to be a list of the stuff you want in it on a sheet somewhere within the workbook
    Does the list have to be vertical or can it be horizontal? I have the lists in a vertical format on "Sheet 1"

    I have done it as shown on your example, with both separate values displayed with spaces and a dash between them, is this what you want?
    If you are referring to the "Overall W/L Record" answer such as 11 - 4 than yes; number _ - _ number is the correct format. Thank you

    I would be interested to know how you would like this to look where 1 of the 2 columns contains a value, while the other does not (this is in your example)
    Ideally i would like Cell C5 to remain blank until A5 and B5 contain values.

    If A5 = a value & B5 = no value than C5 = blank
    If A5 = no value & B5 = value than C5 = blank
    If A5 = no value & B5 = no value than C5 = blank
    C5 = value only when A5 & B5 have values
    Last edited by RachelMads02; 05-05-2017 at 07:00 PM.

  13. #13
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Hello again, sorry for the slow reply....

    1. The dropdown list can be a horizontal range of cells yes, that's why it's good for all the relevant table headers to be on the same row if you want to make a dropdown list that refers to the columns, rather than the rows.

    2. Great

    3. I think you mean if one of the dropdown list cells is empty or blank.....

    In which case....

    =IF(COUNTBLANK(A5:B5)>0,"",<The rest of the formula goes here>)

    Except maybe put the IFFERROR statement as the outer most function, like this.....


    =IFERROR(IF(COUNTBLANK($A$5:$B$5)>0,"",IF($B$5=$E$4,INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0)),IF($B$5=$E$5,INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0))&" - "&OFFSET(INDEX(Table19,MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0)),0,1),IF($B$5=$E$6,ROUND(INDEX(Table19[[Team]:[Win %]],MATCH(Summary!$A$5,Table19[Team],0),MATCH(Summary!$B$5,'Seed Stats'!$A$2:$E$2,0))*100,0)&"%")))),"")

  14. #14
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    Excellent, that works!! Thank you. 1 list down and two to go.

  15. #15
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Dropdown list 2...

    I made a list of the column headers on the summary sheet needed for the dropdown list in B10, this could be hidden on a background sheet or something if you want.

    The dropdown list for A10 just uses the team name column on the Seed Stats sheet.

    The dropdown list for C10 refers to the range E9:E11 just next to it.

    And then the formula in D10.....

    =IFERROR(IF(COUNTBLANK($A$10:$C$10)>0,"",IF($C$10=$E$9,OFFSET(INDEX(Table19,MATCH(Summary!$A$10,Table19[Team],0),MATCH(Summary!$B$10,'Seed Stats'!$A$2:$CG$2,0)),0,1),IF($C$10=$E$10,OFFSET(INDEX(Table19,MATCH(Summary!$A$10,Table19[Team],0),MATCH(Summary!$B$10,'Seed Stats'!$A$2:$CG$2,0)),0,2)&" - "&OFFSET(INDEX(Table19,MATCH(Summary!$A$10,Table19[Team],0),MATCH(Summary!$B$10,'Seed Stats'!$A$2:$CG$2,0)),0,3),IF($C$10=$E$11,ROUND(OFFSET(INDEX(Table19,MATCH(Summary!$A$10,Table19[Team],0),MATCH(Summary!$B$10,'Seed Stats'!$A$2:$CG$2,0)),0,4)*100,0)&"%")))),"")
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    haaa!!! You're awesome!!!!!! That's perfect.

  17. #17
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Thank you, I was happy to help

    Now for dropdown list 3....

    You need to make a list of the column headers from the Conference Stats sheet, to use to make the drop down list for B15. Like I have done for dropdown list 2. I suggest you don't have them as merged cells.

    And then the formula needs to refer to 'table 20' instead of 'table 19' (the same range on the other sheet), the match function for the team name must refer to that sheet also, and refer to row 15 on the Summary sheet instead of row 10.

    Do you want me to do the last one for you too, or can you work it out from what I've done so far?

  18. #18
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    if you could that would be helpful so i can reference the set up for future use.

  19. #19
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    Sure no worries.....

    I unmerged the column header cells on the Conference Stats sheet.

    The dropdown list in A15 refers to column A on the Conference Stats sheet.

    The dropdown list in B15 refers to the list I made for it in column L.

    The dropdown list in C15 refers to the range E14:E16 next to it.

    And then the formula in D15...

    =IFERROR(IF(COUNTBLANK($A$15:$C$15)>0,"",IF($C$15=$E$14,INDEX(Table20,MATCH(Summary!$A$15,Table20[Team],0),MATCH(Summary!$B$15,'Conference Stats'!$A$2:$DZ$2,0)),IF($C$15=$E$15,OFFSET(INDEX(Table20,MATCH(Summary!$A$15,Table20[Team],0),MATCH(Summary!$B$15,'Conference Stats'!$A$2:$DZ$2,0)),0,1)&" - "&OFFSET(INDEX(Table20,MATCH(Summary!$A$15,Table20[Team],0),MATCH(Summary!$B$15,'Conference Stats'!$A$2:$DZ$2,0)),0,2),IF($C$15=$E$16,ROUND(OFFSET(INDEX(Table20,MATCH(Summary!$A$15,Table20[Team],0),MATCH(Summary!$B$15,'Conference Stats'!$A$2:$DZ$2,0)),0,3)*100,0)&"%")))),"")
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    Excellent, thank you very much!!!!

  21. #21
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Drop Down List Multiple Outcome Formula

    I appreciate all your help!
    Last edited by RachelMads02; 05-06-2017 at 06:47 PM.

  22. #22
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Drop Down List Multiple Outcome Formula

    You are welcome

    If there is anything else then ask away.

    If we are done here then please mark the thread as solved.

+ 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. Move a row based on a drop down outcome please help
    By Nickobrien22 in forum Excel General
    Replies: 2
    Last Post: 02-08-2017, 12:11 PM
  2. Replies: 2
    Last Post: 03-11-2014, 03:36 PM
  3. [SOLVED] Formula Problem with Multiple IF / AND Statements and Multiple outcome?
    By JONBOY666 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-22-2013, 01:32 PM
  4. [SOLVED] Formula to solve multiple scenarios with one general outcome
    By pinas in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-22-2012, 09:07 AM
  5. Multiple outcome IF formula
    By dm1983 in forum Excel General
    Replies: 2
    Last Post: 06-20-2010, 12:11 PM
  6. Different outcome for drop down box variables
    By Frankiemillion in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-09-2008, 11:32 PM
  7. Replies: 5
    Last Post: 10-27-2005, 01:55 PM

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