I have a formula that matches the date with a structured table header row. I have extensive code that deals with the text string nature of this row successfully.
However, when trying to check visible values in the column according to date, the array formula I am using works fine for double digit dates like "11-Sep-15" but not effectively single digit like "01-Sep-15". I have even tried not having a leading zero (as it's text) and it still works fine with double digit and not single...
A sample of the formula is as follows;
{=SUM(--( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0))=$D$7)*(--(SUBTOTAL(3,OFFSET( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0)),ROW( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0)))-MIN(ROW( INDEX(SiteAdmin, 0, MATCH(N23&"",SiteAdmin[#Headers], 0)))),,1)))))}
D7 is a check cell with a value of "On" N23 is a check cell where I can play around with text values to test the formula.
I have checked that the value of the header cell is the same as what is being passed to the formula using =SiteAdmin[[#Headers],[01-Sep-15]]=Calcs!N23 and that the value is indeed text by using =ISTEXT(N23)...
Help!
Bookmarks