+ Reply to Thread
Results 1 to 2 of 2

Display column header date when there is data in cell

  1. #1
    Registered User
    Join Date
    08-26-2019
    Location
    Vancouver, Canada
    MS-Off Ver
    MS Office 2019
    Posts
    1

    Display column header date when there is data in cell

    Hi all - this is a bit of weird one, and I'm not sure it's possible. I have an employee leave tracking calendar, with employee names/vacation balances by row, and year-long dates by column displayed along the top. When somebody takes a day off, I would enter "8" in the corresponding cell that matches their name and the correct date. I have a formula that sums their vacation time taken (by row), and subtracts it from their vacation balance for example.

    What I'd like to be able to do, is to write a formula by row that would detect every cell that has data in it (e.g. John Smith x March. 3rd, "8") and spit out the list of dates that person has taken time off. This is really for ease so that I don't have to scroll through my entire worksheet when a manager asks which dates their employees have taken off so far this year. So really, I'd like the formula to "sense" that there's some data in a range of cells, and spit out the cell column headings. Is this possible?

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,141

    Re: Display column header date when there is data in cell

    Hi & welcome to the board.
    Maybe
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Name
    01/01/2019
    02/01/2019
    03/01/2019
    04/01/2019
    05/01/2019
    06/01/2019
    07/01/2019
    08/01/2019
    09/01/2019
    2
    Tom Pearce
    8
    8
    8
    8
    8
    3
    Bill Brewer
    8
    8
    8
    4
    Jan Stewer
    8
    8
    8
    8
    5
    6
    7
    8
    9
    10
    Bill Brewer
    11
    03/01/2019
    12
    04/01/2019
    13
    09/01/2019
    Sheet: Audit
    Formula in B11 copied down
    =IFERROR(INDEX($B$1:$U$1,AGGREGATE(15,6,(COLUMN($B$1:$U$1)-COLUMN($B$1)+1)/(($A$2:$A$4=$A$10)*($B$2:$U$4=8)),ROWS($A$1:$A1))),"")

+ 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. 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
  2. Display Header at next Column
    By Ichigo in forum Excel General
    Replies: 5
    Last Post: 02-04-2015, 02:25 AM
  3. [SOLVED] COUNTA function to count from first cell to date in column header
    By mr_mango81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2015, 01:03 AM
  4. Replies: 7
    Last Post: 07-25-2014, 05:08 PM
  5. Replies: 1
    Last Post: 07-22-2014, 08:27 PM
  6. Replies: 8
    Last Post: 03-07-2014, 03:12 PM
  7. [SOLVED] Count cell values under column with today's date header
    By jben86 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-23-2013, 03:19 PM

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