I'm trying to have a list of event sponsors automatically generate from the full RSVP list. I copied my formula from another spreadsheet that works perfectly and modified it for the sponsor criteria instead of the original date criteria. It worked when I had one sponsor on the second line, but failed when I added another sponsor to the list. Some experimentation reveals that it works as long as I have sponsors only on the first two lines, if I add a sponsor anywhere else, it fails. I can tell from running Evaluate Formula that the failure is caused by the offset section of the formula, but I can't figure out how to fix it. I tried changing the COUNT reference from the actual section to the full column as in the original, but that made no difference. The current and original formulas are below and a sample workbook is attached. Thanks in advance for any help.
Current formula (Music!A9:A21 on sample):
{=IFERROR(INDEX(Attendees!$A$3:$A$23,SMALL(IF(Attendees!$F$3:$F$23<>0,ROW(OFFSET(Attendees!$A$1,0,0,COUNT(Attendees!$A$3:$A$23)+2,1))),ROWS($A$9:A9))),"")}
Original formula from another sheet:
{=IFERROR(INDEX('All Donations'!$A$7:$A$26,SMALL(IF('All Donations'!$C$7:$C$1623>=(EOMONTH($B$3,-1)+1),IF('All Donations'!$C$7:$C$1623<=$B$3,ROW(OFFSET('All Donations'!$A$1,0,0,COUNT('All Donations'!$A:$A)+6,1)))),ROWS($A$26:A26))),"")}
Bookmarks