+ Reply to Thread
Results 1 to 4 of 4

Update cell of filtered column header selection

  1. #1
    Registered User
    Join Date
    09-11-2018
    Location
    South Africa
    MS-Off Ver
    Microsoft Office 365
    Posts
    7

    Update cell of filtered column header selection

    Good day all,

    I would very much like to have a certain cell reference(A4) be updated with the team leader I select on the header filters.
    Reason being, I have to print out the whole team for every team leader, and have the leader's name on top of the list.

    I have a database with staff members belonging to different team leaders, for instance 30 random members belonging to a certain leader.
    This database has many headers, of which the TEAM header is of importance here.

    The structure looks like this:

    CELL A4, NAME OF SELECTED TEAM ON HEADER FILTER.

    ROW NUMBER COLUMN A COLUMN B COLUMN NQ
    5 "NAME" "SURNAME" "TEAM"
    6 RANDY DE BROGLI JAMES
    7 SANDY REYNOLDS JAMES
    8 GILL ABRAHAMS JAMES


    So as I click the filter on the header "TEAM", cell ref NQ5, and select "Team leader 1", CELL A4 should automatically update with "Team leader 1".

    Here is what I have tried so far, and it does not seem to solve the problem all that well:

    =INDEX(TEAM,MIN(IF(SUBTOTAL(3,OFFSET(NQ5,ROW(TEAM)-ROW(NQ5),0)),ROW(TEAM)-ROW(NQ5)+1)))

    I have made the range of Cells NQ6:NQ999 a defined name, called TEAM.
    What this formula is trying to accomplish, is to look at the 1st cell below the header "TEAM", and display the result in cell A4.
    You can imagine if I have multiple names in a list, all belonging to the same team, "James", and if I select "James" under team,
    The first cell under the header will in fact say "James".

    Hope this explains well enough what I am trying to convey.

    Any help would be awesome!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Update cell of filtered column header selection

    Hi Cyberpunk001. Welcome to the forum.

    To best describe your question it is best to upload a small Excel file representative of the challenge. You usually get more/faster response if you do.

    Include a BEFORE section and an AFTER section of expected outcomes.

    If you do not know how to do this:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    09-11-2018
    Location
    South Africa
    MS-Off Ver
    Microsoft Office 365
    Posts
    7

    Re: Update cell of filtered column header selection

    Hi FlameRetired,

    Thanks so much for the quick reply, and sound advice!

    I have been waiting for a reply from another forum for weeks on end.

    Don't know what I did wrong before, but now the solution works!
    This is an array formula that I forgot to mention.

    Hope this solution helps someone looking for this particular answer.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Update cell of filtered column header selection

    I don't understand:
    1. Why do you need that formula in 'After' A4?
    2. Why are there 2 separate definitions of TEAM in Name Manager?

    If these things can be changed:
    1. Use a drop down to select Team Name in A4.
    2. Delete the TEAM definition for 'After' sheet in Name Manager.

    I believe I have solution for this.

    As it stands those things defeat the formulas I have tried.

+ 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. Cell reference to filtered data auto update as Filtered result changes
    By Irajoo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-22-2017, 02:17 AM
  2. [SOLVED] Autofit to fully display header in a filtered column
    By jomili in forum Excel General
    Replies: 13
    Last Post: 07-10-2015, 01:26 PM
  3. macro to lock cell by column header based on value in another cell by column header
    By Closet Guru in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-03-2015, 12:36 PM
  4. Selection of Excel table column based on a header
    By Alpana Savarna in forum Excel General
    Replies: 3
    Last Post: 10-16-2014, 09:43 AM
  5. [SOLVED] Obtain cell reference below the header of a filtered range.
    By Peter930 in forum Excel General
    Replies: 1
    Last Post: 12-21-2013, 06:09 PM
  6. export filtered data (including header row) to new workbook, maintain column width
    By ovetcobo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2013, 03:47 PM
  7. [SOLVED] Copy a specific column data of filtered output without header
    By uvaidya in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-05-2012, 09:21 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