+ Reply to Thread
Results 1 to 5 of 5

Return the column of an array based on 1st cell

  1. #1
    Registered User
    Join Date
    12-09-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    Microsoft Office Standard 2013
    Posts
    3

    Return the column of an array based on 1st cell

    I've done some searching and I'm part way to my answer but getting a bit stuck.

    Essentially I have a combo box to select an option e.g. N, F, FX, FY. On another sheet I have an array with numbers referring to these items (N=1, F=2, Fx=3 etc) and data below these in a column that I need to return as an array. This array feeds in as the range for the next combo box - e.g. select N in first box, second box finds column 1 and uses that column except the header as its data range.

    The attached spreadsheet has my efforts so far. I've managed to return the first cell in the column but I need the rest below it too.

    I know this should be relatively simple but I'm tearing my hair out. Any help would be much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Return the column of an array based on 1st cell

    Is this what you are trying to do - load combos from the four lists in order.
    If so you have not formatted the last two combos.
    Capture.JPG
    Attached Files Attached Files

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Return the column of an array based on 1st cell

    Any reason why you want a Combo Box instead of a straightforward dropdown box?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    12-09-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    Microsoft Office Standard 2013
    Posts
    3

    Re: Return the column of an array based on 1st cell

    Quote Originally Posted by torachan View Post
    Is this what you are trying to do - load combos from the four lists in order.
    If so you have not formatted the last two combos.
    No, the 3rd combo box (sub-series) needs to change the list it shows based on what the 2nd box (series) shows. So if you select N, it shows the NQR, NPR etc list. If you select GIGA it shows CXY, CXZ, EXY in the sub-series box instead.

    This is why I'm trying to get a formula that changes the range based on the value of the series box.





    Quote Originally Posted by Glenn Kennedy View Post
    Any reason why you want a Combo Box instead of a straightforward dropdown box?
    Yes - some pretty basic level users will be using this spreadsheet and if I use a data validation drop-down list it doesn't show the drop down arrow unless you click on the cell. This won't work as they'll get confused. I know there are work arounds to show a dummy button but that won't work with the rest of the format I need to do. Regardless, needing a dynamic range that changes based on what is selected in the box above is still a requirement and the method to do it won't change between those 2 options.

  5. #5
    Registered User
    Join Date
    12-09-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    Microsoft Office Standard 2013
    Posts
    3

    Re: Return the column of an array based on 1st cell

    All good, problem solved.

    In the formula I had all I needed to do was for the index function change the "row" value to 0 instead of 2 so it returns the entire column rather than the cell corresponding to row 2 of that column.

+ 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] how can i make this array change the cell in a column based on a text array
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2016, 09:44 AM
  2. Return row & column number from cell in 2d array
    By ranald in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-13-2013, 05:27 PM
  3. [SOLVED] Return Category based on Keywords in a cell from array
    By nazsha07 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2012, 12:25 AM
  4. Replies: 2
    Last Post: 02-27-2010, 11:17 AM
  5. Replies: 7
    Last Post: 04-16-2009, 01:03 PM
  6. Return a cell value based on specific combinations of cells in an array
    By rmcnam05 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2005, 11:05 PM
  7. return array result in cell based on comparing dates
    By Ruthki in forum Excel Formulas & Functions
    Replies: 51
    Last Post: 09-06-2005, 12:05 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