Formula only recognizes one submission
My goal is to make it so that the spreadsheet checks to see if the respective Web ID in Column B has an Active LOA as determined in Column I. An Active LOA being defined as one with an end date that has NOT yet passed.
I've gotten the formulas to work with the first entry. But if that first entry's date has passed (marked as Expired) and the entry after that one hasn't passed (marked as Active LOA) then the formula breaks down. It's as if the formula works only once.
The formula that checks the respective Web ID's in Column B of the "Roster" tab against the Web ID in the "ScD LOA Responses" tab is in cell F9 of the "Roster" tab.
The formula that determines whether or not the date has passed/whether the person is still on LOA is found in column I of the respective row.
You are getting wrong result on second row because you have lock the range on first row, change it as following should work properly:
=IF($F3:$F<TODAY(),"Expired","Active LOA")
You may try arrayformula also by only enter formula on F3:
=arrayformula(if($F3:$F="","",IF($F3:$F<today(),"expired","active")))
The issue for Roaster is not syn with the form submission is because Vlookup is limited to return first occurence unless you make so adjustment, here I will give a simple solution to always return the latest result for syn:
=iferror(index(FILTER('SC LOA Response'!C$3:$I,'SC LOA Response'!C$3:C=B6),COUNTIF('SC LOA Response'!C$3:C,B6),7),"")
Since your roster is fixed number, you can copy the formula on every row, hope it solve your issue :)
