+ Reply to Thread
Results 1 to 3 of 3

Returning data from a column, based on a date that references multiple columns and a row

  1. #1
    Registered User
    Join Date
    12-09-2021
    Location
    Los Angeles, CA
    MS-Off Ver
    Google Sheets
    Posts
    2

    Returning data from a column, based on a date that references multiple columns and a row

    Hi folks -

    I'm working on automating a schedule template for work. On one page, I have the employee names, roles, and dates as headers. If an employee works, they have either an 'X' for for a full day or an 'H' for a half day.

    On the second tab, I want to be able to select a date, then have the names of everyone that works listed automatically. I've attached a (very simplified) sample here. So basically, when a date is selected in B1 of the schedules tab, anyone that works on the date selected is listed in column 'A'.

    I've been trying to figure out how to make this work, and am wondering if it's possible. I think it must be a series of nested IF statements, maybe with an index match? Otherwise perhaps an array?

    I'm working on Microsoft Office 365.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by misgett; 12-10-2021 at 12:13 AM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,373

    Re: Returning data from a column, based on a date that references multiple columns and a r

    For Name, put this on cell A2 on second tab then press all together CTRL+ALT+ENTER together because this an array formula then copied down:

    =IFERROR(INDEX(Staff!$A$2:$A$9,SMALL(IF(INDEX(Staff!$E$2:$K$9,0,MATCH($B$1,Staff!$E$1:$K$1,0))<>"",ROW(Staff!$A$2:$A$9)-ROW(Staff!$A$2)+1),ROWS($A$1:A1))),"")

    For Role, put this on cell B2 on second tab then press all together CTRL+ALT+ENTER together because this an array formula then copied down:

    =IFERROR(INDEX(Staff!$C$2:$C$9,SMALL(IF(INDEX(Staff!$E$2:$K$9,0,MATCH($B$1,Staff!$E$1:$K$1,0))<>"",ROW(Staff!$A$2:$A$9)-ROW(Staff!$A$2)+1),ROWS($A$1:B1))),"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-09-2021
    Location
    Los Angeles, CA
    MS-Off Ver
    Google Sheets
    Posts
    2

    Re: Returning data from a column, based on a date that references multiple columns and a r

    Azumi that definitely works, thanks so much! Can you explain it to me? I was trying to parse through it to understand it but got lost.

+ 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. returning date from column based on max value in a row
    By gdarling in forum Excel General
    Replies: 4
    Last Post: 01-13-2021, 11:42 AM
  2. [SOLVED] Returning Value based on references in row and column headers
    By uvebeenwarrened in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-30-2017, 03:29 PM
  3. [SOLVED] Copying data from one column to another based on two references of columns
    By catscats11 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-27-2017, 11:37 AM
  4. [SOLVED] Returning value from one column based on two neighboring columns
    By Ezzard in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-26-2014, 11:50 AM
  5. [SOLVED] Macro to Sum Data in Multiple Columns based off Date in Column A
    By bdt99 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2013, 06:13 PM
  6. [SOLVED] Returning value from a third column; based on two other columns.
    By tony8980 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2012, 12:32 AM
  7. Replies: 5
    Last Post: 05-04-2011, 06:51 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