Geoff asks:

Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., filled or not). Is there anyway to pull a list of names in a canned daily report based on the date and whether the cell is filled? In other words, I want to lookup the y-axis headers as opposed to the cross reference value.

Thanks, Geoff

Answer

Formula in cell B16:

=TODAY()

Array formula in cell B19:

=IFERROR(INDEX($A$2:$A$14, SMALL(IF(INDEX($B$2:$AF$14, 0, MATCH($B$16, $B$1:$AF$1, 0))="x", MATCH(ROW($A$2:$A$14), ROW($A$2:$A$14)), ""), ROW(A1))), "")

How to create an array formula

  1. Select cell B19
  2. Paste array formula in formula bar
  3. Press and hold CTRL + SHIFT
  4. Press Enter

How to copy array formula

  1. Select cell B19
  2. Copy (Ctrl + c)
  3. Select cell range B20:B25
  4. Paste (Ctrl + v)

Download excel *.xlsx file

Shift scheduling.xlsx

Shift schedule - Conditional formatting

Conditional formatting - Cell range B2:AF14

  1. Select cell range B2:AF14
  2. Go to tab "Home"
  3. Click "Conditional formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Enter this formula:=B$1=$B$16
  7. Click "Format.." button
  8. Go to tab "Fill"
  9. Pick a color
  10. Click OK
  11. Click OK

Conditional formatting - Cell range A2:A14

  1. Select cell range A2:A14
  2. Go to tab "Home"
  3. Click "Conditional formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Enter this formula:=INDEX($B$2:$AF$14, ROW(A2)-1, MATCH($B$16, $B$1:$AF$1, 0))="x"
  7. Click "Format.." button
  8. Go to tab "Fill"
  9. Pick a color
  10. Click OK
  11. Click OK

 

Recommended blog posts

Quickly highlight records in a list using multiple criteria in excel
Quickly highlight records in a list in excel (AND logic)
Quickly highlight records containing text strings in excel (AND Logic)