Please forgive as I am a novice over my head. Not even sure how to word the post.
Below is the current array formula I have and it works in previous worksheets. The new worksheet differs in thas the letters after the "2-" changing all the time, so I only want to countif "2-*.
=IF(COUNTIF(TEST_TIME7, "2-WAC")>=ROWS($1:$1),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$1))),"") The second and third formula are the same but the ROWS($1:$1) change to ROWS($1:$2) and then 3.
TEST_TIME7 is one of the columns of assignments for the 7 o'clock period.
TEST_NAMES is the column with the workers names to return from.
This worksheet is a little different in that after the "2-" the remaining letters now vary. (Before they were the same every time. I just want to still count it if it has the "2-with anything else". I have tried the * (ie. "2-*) and get a #NUM! error.
I use the ROW to look for the second and third time it occurs in the same column.
Full Worksheet has about 40 rows and 11 columns.
TEST
NAMES TEST_TIME
7AM
NAME 1 2-WAC =IF(COUNTIF(TEST_TIME7,"2-WAC")>=ROWS($1:$1),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$1))),"")
NAME 2 2-WAC =IF(COUNTIF(TEST_TIME7,"2-WAC")>=ROWS($1:$2),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$2))),"")
NAME 3 2-WAC =IF(COUNTIF(TEST_TIME7,"2-WAC")>=ROWS($1:$3),INDEX(TEST_NAMES,SMALL(IF(TEST_TIME7="2-WAC",ROW(TEST_NAMES)-4),ROWS($1:$3))),"")
Bookmarks