Array not displaying result after moving data

Hi,

Hope you guys can help me out here

I "borrowed" an example array for a shift schedule where X axis is date and Y axis is names. It displays the names of anyone with an X in the array for the chosen date

On my sheet, the positioning is slightly different to the example and the names column is populated by a pivot table which references a table on another sheet

Despite matching up the location references from the example array formula to my sheet, the formula refuses to display any names when there's an X in the array

The formula used is =IFERROR(INDEX($B$6:$B$30,SMALL(IF(INDEX($C$6:$AG$30,0,MATCH($B$33, $C$5:$AG$5, 0))="x",MATCH(ROW($B$6:$B$30),ROW($B$6:$B$30)),""),ROWS($A$1:A1))), "")

I've tried Ctrl Shift Enter and I've tried replacing the pivot table with a different reference table at the last column instead and referencing that instead with no luck

I've attached the example sheet I borrowed from (Shift-Schedulingv3) and my sheet (Broken Array)- I'm fairly sure the pivot table data stays intact although it doesn't have the reference page anymore

I'm using Excel 2010

Any ideas?

Remove all merged cells. Then try.

The date from the activex control is being treated as text, rather than a numeric date.

Link the active control to A33 rather than B33.
In B33 use the formula
=A33 * 1

I simplified the formula in C5:
=DATE($E$1,MONTH($J$1&1),1)

In A36, copied across and down - an ordinary formula:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$6:$C$30)/(INDEX($C$6:$AG$30,,MATCH($B$33,$C$5:$AG$5,0))="X"),ROWS($1:1))),"")

There is something wrong with B33. It is not a date. It is text that just looks like one. Whatever way you are using to choose it, you need to change it to return a real date.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

If you used my formula, there is no need to change anything. Just drag it across and down.