Hello there
I have a set of sports fixtures and I have a brilliant formula (provided by Fairwinds on this messageboard) to the left of each fixture which automatically calculates the date given the start date of season, the frequency of the matches and including free dates:
the formula is IF(OR($Q$5="",H7=""),"",IF($R$5=14,SMALL(IF(ISNA(MATCH(ROW($1:$200)*$R$5-$R$5+$Q$5,$S$5:$S$14,0)),(ROW($1:$200)*$R$5-$R$5+$Q$5),""),H7),SMALL(IF(ISNA(MATCH(ROW($1:$200)+$Q$5-1,$S$5:$S$14,0))*ISNUMBER(MATCH(WEEKDAY(ROW($1:$200)+$Q$5-1,2),$R$5:$R$7,0)),(ROW($1:$200)+$Q$5-1),""),H7))) entered as an array
Q5 is the start date
R5 is an indicator of frequency
S5 to S14 are the free dates
I am trying to amend the formula even more to allow extra dates to be included so that most games would be perhaps on a Saturday but there may be a particular date, perhaps a bank holiday Monday when another batch of games could be included.
I know its a bit of an 'ask' but any help would be appreciated.
I have a set of sports fixtures and I have a brilliant formula (provided by Fairwinds on this messageboard) to the left of each fixture which automatically calculates the date given the start date of season, the frequency of the matches and including free dates:
the formula is IF(OR($Q$5="",H7=""),"",IF($R$5=14,SMALL(IF(ISNA(MATCH(ROW($1:$200)*$R$5-$R$5+$Q$5,$S$5:$S$14,0)),(ROW($1:$200)*$R$5-$R$5+$Q$5),""),H7),SMALL(IF(ISNA(MATCH(ROW($1:$200)+$Q$5-1,$S$5:$S$14,0))*ISNUMBER(MATCH(WEEKDAY(ROW($1:$200)+$Q$5-1,2),$R$5:$R$7,0)),(ROW($1:$200)+$Q$5-1),""),H7))) entered as an array
Q5 is the start date
R5 is an indicator of frequency
S5 to S14 are the free dates
I am trying to amend the formula even more to allow extra dates to be included so that most games would be perhaps on a Saturday but there may be a particular date, perhaps a bank holiday Monday when another batch of games could be included.
I know its a bit of an 'ask' but any help would be appreciated.