+ Reply to Thread
Results 1 to 14 of 14

IF, OR, CHOOSE problem

  1. #1
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    IF, OR, CHOOSE problem

    I attached a spreadsheet because I'm certain that I'm going to butcher this explanation. I Two Choices...First Choice and Second Choice. I make a selection in the First Choice cell, and it draws information from adjacent columns thru Data Validation. Select A or B and it chooses Selection 1. Select C or D and it chooses Selection 2. When you choose E or F you have 3 selections to choose from. I don't know how to make this happen. Please see attached for all of this to make sense. Thanks all for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,163

    Re: IF, OR, CHOOSE problem

    Hi Jim,

    You just need to know this is called Cascading Dropdown Lists or Dependent Dropdown Lists. Search on these words using Excel and/or read:

    https://www.ablebits.com/office-addi...n-lists-excel/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,927

    Re: IF, OR, CHOOSE problem

    May be paste Options in front of section like below.
    Excel 2016 (Windows) 64 bit
    D
    E
    F
    G
    H
    I
    J
    K
    3
    First Choice Option A Selection 1 Option A Option B
    4
    Option E Option B Selection 2 Option C Option D
    5
    Option C Selection 3 Option E Option F
    6
    Second Choice Option D Selection 4 Option E Option F
    7
    Selection 1 Option E Selection 5 Option E Option F
    8
    Option F
    9
    Sheet: Sheet1

    N3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy down.

    Create a name manger press Alt-M-N->New-Give name DropDown-> Paste =OFFSET(Sheet1!$N$3,,,COUNTIFS(Sheet1!$N$3:$N$34,"?*")) this in refer to then ok.

    On D7 cell press Alt-A-V-V-List- Press in source F3 and choose Dropdown then ok.

    Excel 2016 (Windows) 64 bit
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    2
    Helper Column
    3
    First Choice Option A Selection 1 Option A Option B Selection 3
    4
    Option E Option B Selection 2 Option C Option D Selection 4
    5
    Option C Selection 3 Option E Option F Selection 5
    6
    Second Choice Option D Selection 4 Option E Option F
    7
    Selection 1 Option E Selection 5 Option E Option F
    8
    Option F
    9
    10
    11
    12
    13
    Sheet: Sheet1
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Thanks for your help and apologies for the delayed response. I think this points me in the right direction so I'm going to go ahead and mark this solved for now. Thanks again.

  5. #5
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Hi all. This is pertaining to Conditional Formatting as I understand it. I attached a spreadsheet to help with my issue. I previously had this marked as solved while I tried to work through it. Have spent a while messing with it and I'm not getting the success that I'd hoped for. The previous suggestions seem useful, however I'm attempting to do all of this without using "Helper Columns" and without changing the Layout. The spreadsheet I attached is a summarized duplicate of the one I'm actually working on. I read through the material, did some research, and tried to teach myself some things. I tried selecting the according cells, and assigning them a name with data validation, then incorporating that into the existing formula. I just used the name "CUP" temporarily. Currently however, the only selection it's yielding back, is the last one in the range of cells; Selection 5 (Selection 3 : Selection 5 or H5:H7). Can someone let me know if I'm on the right track or what I might be doing wrong. Please and thank you for your help.

    =IF(OR(D4=F3,D4=F4),H3,IF(OR(D4=F5,D4=F6),H4,IF(OR(D4=F7,D4=F8),CUP,"")))
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,197

    Re: IF, OR, CHOOSE problem

    im not sure about your explaination but try the following as d7 validation as a list

    put the below as a source
    =IF(OR(D4=F3,D4=F4),H3,IF(OR(D4=F5,D4=F6),H4,IF(OR(D4=F7,D4=F8),OFFSET(H5,0,0,3,1),"")))

    any closer?

  7. #7
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Hello. Thanks for your help. Unfortunately it's still giving me the same result. What I'm trying to achieve is the following:
    Select "Option A or Option B", get "Selection 1"
    Select "Option C or Option D", get "Selection 2"
    Select "Option E or Option F", get a drop down menu to pick "Selection 3, 4, or 5"
    Conditional Formatting is a bit beyond my level of knowledge so I'm sort of just plugging things in right now to see what works.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,890

    Re: IF, OR, CHOOSE problem

    Try:

    3 named ranges: "Selection_1"","Selection_2","Selection_35"

    in Data Validation:

    Allow: =List

    Source: =IF(OR(D4=F3,D4=F4),Selection_1,IF(OR(D4=F5,D4=F6),Selection_2,Selection_35))
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Hello. Thanks for the reply. Pretty sure I understand what you're trying to do, but it still doesn't seem to be working. The spreadsheet that you attached does contain the drop down list in cell D7, but it doesn't seem to be contingent upon the selections made in D4. Also it looks like when a selection is made in D4, there's no longer any change in D7. I did try to modify what you gave me with what I already had (below), but when I selection Option E or F it's still just choosing Selection 5, and not giving me a drop down to select 3,4,or 5 (attached). Thanks again for all your help. It's very much appreciated.

    =IF(OR(D4=F3,D4=F4),Selection_1,IF(OR(D4=F5,D4=F6),Selection_2,IF(OR(D4=F7,D4=F8),Selection_35,"")))
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Clarifying on my previous posts...I don't want there to be a drop down list in D7 100% of the time, but only when prompted by the selection made in D4. Tks.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,890

    Re: IF, OR, CHOOSE problem

    You either have drop down list or don't: you cannot swap between a single cell and a drop down list; feasible with VBA (I suspect).
    Last edited by JohnTopley; 04-20-2018 at 02:04 PM.

  12. #12
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Understood...like I said, still learning. Appreciate the help. Tks.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,890

    Re: IF, OR, CHOOSE problem

    Please Login or Register  to view this content.
    See attached: select "First Choice"
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    02-12-2018
    Location
    TX
    MS-Off Ver
    MS Office 2019
    Posts
    305

    Re: IF, OR, CHOOSE problem

    Apologies for the delayed response. This is EXACTLY what I've been looking for, and I've been looking everywhere for it too. I really appreciate your help. Now I just need to figure out how to add this to my other spreadsheet...that should be fun.

+ 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. VBA FromList Choose Problem
    By rbirch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 07:25 PM
  2. [SOLVED] Help with the Choose Function to choose a col
    By dlow in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-27-2014, 03:11 PM
  3. Two validations: first choose the sheet (1-5), then choose the option
    By bee88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2014, 08:12 AM
  4. [SOLVED] Formula help to choose lowest cost from multiple vendors and then choose vendor
    By roland_arv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2013, 07:48 PM
  5. VLOOKUP ? IFERROR ? CHOOSE ? Problem
    By yesitskez in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 05-29-2013, 11:09 AM
  6. Problem with the Choose function
    By thetexan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-30-2013, 03:13 PM
  7. [SOLVED] CHOOSE Function (Automatic) - Automatic Choose Array
    By dluhut in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-13-2013, 03:22 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