In the attached, on the
Housewide sheet there are some formulae A8:B15 and A108:B115.
These formulae are committed to the sheet using
Ctrl+Shift+Enter, not just
Enter.
I leave you to enter similar formulae elsewhere in the sheet.
The formula is along the lines of:
Code:
=IFERROR(INDEX('2nd'!$B$5:$B$20,SMALL(IF(INDEX('2nd'!$C$5:$AR$20,0,MATCH($A$3,'2nd'!$C$4:$AR$4,0))="a",ROW('2nd'!$B$5:$B$20)-4,FALSE),ROW()-7)),"")
'2nd'!$B$5:$B$20 The list of names. Appears twice in the formula.
'2nd'!$C$5:$AR$20 The body of the table containing
as,
ps and blank cells.
$A$3 The date
'2nd'!$C$4:$AR$4 The dates at the top of each table.
-4 The
4 is one less than the top row of '2nd'!$B$5:$B$20
-7 The
7 is one less than the row that the topmost formula is in.
In each section of your
Housewide sheet you can compose the formula in the first cell, then either:
array-enter it, and drag/copy down as far as you need,
or:
select that cell and the handful of cells beneath it, and array-enter the formula into all the selected cells at once. This will be lighter on resources. I've done this with B108:B115 (light blue shading).
It may not be the most elegant formula, it's just the one that came out right first.
I've highlighted in light green other adjusted cells; TODAY() has been replaced with a hard date so we can see some results.
I've also populated the sheets with fictitious names and a bunch of
ps and
as in the tables.
Tenet Health will be happy.