Array Formula with dynamic column search

[karlhalterman]

Greetings all and thanks for looking at this post for me. I am using an array formula to aggregate some data from another sheet for printing purposes for a 3 week block from a FY calendar. Rows in my sheets are student counts for classes, the columns (H:JN) are dates for schedules.

The current formula works as written here:

{=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(NOFLY_CRITERIA,S$2)))))>0,"", SUM(IF(FY_CALENDAR!$G23:$G71="STUDENTS",FY_CALENDAR!S23:S71)))}

The basic calculation is that first it checks whether the day is a weekend or holiday and returns empty if so, if not, then it goes to my calendar and pulls a sum of total students assigned on that day. In its current form, it works fine because the dates on this sheet are in the same columns as the dates on the FY calendar sheet.

The problem is now that I would like to be able to change the dates on this sheet to project a future date, so any date can be selected. That updates my columns on this sheet to a dynamic 3 week range. However, the formula no longer pulls the right data. I need to update the last argument of my SUM(IF function to find the column that has the date that matches the column from this sheet. INDEX/MATCH are always my go to, but I've never gotten them to work in an array formula. I am attempting to use an INDIRECT/ADDRESS method, but am getting errors and would love some assistance getting the syntax right... here is what I have:

T$6 = the date on this sheet for the current calculation
$6:$6 = My row of sequential dates on my FY calendar
Rows 23 and 71 in the address blocks are the row range for this syllabus to search in

{=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(NOFLY_CRITERIA,T$2)))))>0,"", SUM(IF(FY_CALENDAR!$G23:$G71="STUDENTS",INDIRECT(ADDRESS(23, MATCH(T$6, FY_CALENDAR!$6:$6,0),,,FY_CALENDAR)&":"& ADDRESS(71, MATCH(T$6, FY_CALENDAR!$6:$6,0),,,FY_CALENDAR)))))}

Any help would be appreciated. best, current result is #NAME?

[63falcondude]

Hello and welcome to the forum.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[karlhalterman]

Haha, nevermind. Solved my own problem pretty fast, was a matter of moving the sheet name to the front of the Indirect call:

{=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(NOFLY_CRITERIA,T$2)))))>0,"", SUM(IF(FY_CALENDAR!$G23:$G71="STUDENTS",INDIRECT("'FY_CALENDAR'!" & ADDRESS(23, T$7,1,1)&":"& ADDRESS(71, $T7,1,1)))))}

[63falcondude]

I'm glad you got it figured out.

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