+ Reply to Thread
Results 1 to 5 of 5

Sorting with name ranges and indirect

  1. #1
    Registered User
    Join Date
    01-17-2020
    Location
    France
    MS-Off Ver
    2010 and 2016
    Posts
    3

    Sorting with name ranges and indirect

    Hello Excel lovers,

    I think the picture and the file is pretty clear and visual to understand my need.

    I have 3 teams with some operators in each.

    With the use of the name range and indirect, i can correctly sort, but i need to set the database like this :

    Team1
    Op1Team1
    Op2Team1
    ...

    I need my operators to stay in the same cell,
    But he can go to the Team2 or Team 3

    Doable with name range and indirect?


    Capture.PNG


    Thank you
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-17-2020
    Location
    France
    MS-Off Ver
    2010 and 2016
    Posts
    3

    Re: Sorting with name ranges and indirect

    OK, so almost done.


    Last thing to do is to remove the blancks of the List.



    Capture2.PNG
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Sorting with name ranges and indirect

    Quote Originally Posted by subzero42 View Post
    . . . I think the picture and the file is pretty clear and visual to understand my need. . . .
    You would be wrong, which is likely why no one else has responded before this.

    Looks like you want to be able to change the membership of the teams in your 'database' in K24:L42 by changing Team entries in L25:L42, then have that flow into the table in W23:Y42. Note: that should be W24:Y42 because there are 18 original records, and row 25 to 42 spans 18 rows, and adding a row of column labels at the top means the table could begin in row 24.

    If so, then the task is populating W25:Y42 from K25:L42. Not that difficult.

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


    Copy W25 and paste into W25:Y42.

    This is rather inefficient using no supporting formulas. Better to use some. For example, using N25:N42 and W21:Y22,

    N25: =LOOKUP(L25,{"Blue",100;"Green",300;"Red",200})+COUNTIF(L$25:L25,L25)

    Fill N25 down into N26:N42,

    W21: =COUNTIF($L$25:$L$42,W24)
    W22: =LOOKUP(W24,{"Blue",100;"Green",300;"Red",200})

    Select W21:W22 and right into X21:Y22. Then you could use simpler and more efficient formulas like

    W25: =IF(ROWS(W$25:W25)<=W$21,INDEX($K$25:$K$42,MATCH(W$22+ROWS(W$25:W25),$N$25:$N$42,0)),"")

    Copy W25 and paste into W25:Y42.
    Last edited by hrlngrv; 01-17-2020 at 10:06 PM.

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Sorting with name ranges and indirect

    =IF(COUNTIF($L$25:$L$42;$D$29);OFFSET(M25;;;COUNTIF($L$25:$L$42;$D$29))) in to data validation
    and array formula in to M25:M29
    =IFERROR(INDEX($K$25:$K$42;SMALL(IF($L$25:$L$42=$D$29;ROW($L$25:$L$42)-ROW($L$24));ROW(A1)));"")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-17-2020
    Location
    France
    MS-Off Ver
    2010 and 2016
    Posts
    3

    Re: Sorting with name ranges and indirect

    Thank you for your time guys,

    You would be wrong, which is likely why no one else has responded before this.
    OK, Sorry, i'll try to be more specific if there's a next time.

    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. Sorting 2 data ranges by comparing one column in each and sorting to match
    By MDKsmiffy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 03:30 PM
  2. [SOLVED] INDIRECT Query: How to incorporate INDIRECT to choose between two ranges?
    By The_Snook in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-18-2012, 06:28 AM
  3. INDIRECT and dynamic ranges
    By inky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2007, 04:08 PM
  4. INDIRECT(ADDRESS(...)) in ranges
    By nsv in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2007, 02:39 AM
  5. Dynamic Ranges using INDIRECT
    By JAP in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-22-2005, 09:00 AM
  6. Pivot ranges and INDIRECT
    By daveydave60 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2005, 08:06 AM
  7. Pivots ranges and INDIRECT
    By daveydave60 in forum Excel General
    Replies: 0
    Last Post: 04-21-2005, 10:59 PM
  8. [SOLVED] Indirect and dynamic ranges
    By Sam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2005, 03:06 AM

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