CODEWITHSUNDEEP
excelexcel-formula
Andrew Wiggin
Andrew Wiggin

Reputation: 11

How to create a reference to a range of a variable number of columns within a formula?

I am creating a schedule for a long long list of clients whos frequency varies. The frequencies of visits vary from every three months to once a year. I am creating a formula that will populate a schedule for January through June of the following year with the clients that must be seen each month. I have client names on top (January in D2, Feb in E2, etc.) and the list of names below.

I am trying to filter with one of the criteria being that it is not present in the past x months (columns), where x is the frequency, but if the month is March and the client has appointments every 5 months, the past 3 months should be searched, instead of 5.

The below formula repeatedly gives a #VALUE! error due to the INDIRECT in the line COUNTIF(IF(COLUMN() - Schedule!$G$2:$G$262 < 4, $D$3:D$264, INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN() - 1,4),"1","") & "2:" & SUBSTITUTE(ADDRESS(1,COLUMN() - Schedule!$G$2:$G$262,4),"1","") & "264")), Schedule!$C$2:$C$262 ) = 0.

=FILTER( Schedule!$C$2:$C$262, ( IFERROR( DATEDIF( Schedule!$F$2:$F$262, DATE( $B$3, MONTH( DATEVALUE( E$2 & "1" ) ), 1 ), "M" ), ( 0 - DATEDIF( DATE( $B$3, MONTH( DATEVALUE( E$2 & "1" ) ), 1 ), Schedule!$F$2:$F$262, "M" ) ) ) >= Schedule!$G$2:$G$262 ) * (COUNTIF(IF(COLUMN() - Schedule!$G$2:$G$262 < 4, $D$3:D$264, INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN() - 1,4),"1","") & "2:" & SUBSTITUTE(ADDRESS(1,COLUMN() - Schedule!$G$2:$G$262,4),"1","") & "264")), Schedule!$C$2:$C$262 ) = 0 ) * (Schedule!$E$2:$E$262 = $B$6) * ( Schedule!$F$2:$F$262 <> "" ), "" )

Any help would be greatly appreciated :).

Upvotes: 0

Views: 57

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

I converted your Schedule into a Table and used the following formula: (You can convert the structured references to cell addresses if you prefer)

=IFERROR(FILTER(tblSchedule[[Name]:[Name]],
     (MOD(DATEDIF(tblSchedule[[Last Review]:[Last Review]],DATE($B$3,COLUMN()-2,0),"m"),tblSchedule[[Frequency]:[Frequency]])=0)*
     (DATEDIF(tblSchedule[[Last Review]:[Last Review]],DATE($B$3,COLUMN()-2,0),"m")<>0)),"")

The results are different than your formula, but they seem to be congruent with your schedule data.

The month number is derived from the column number and the Year in B3 to create the comparison date. enter image description here

Upvotes: 0

Related Questions