I have a question regarding calculated fields. I have a created a calculated field that populates drop down options based on items from another sharepoint list if they meet certain conditions. The use case is the following:
- List 1: It's a list with candidates for potential hiring, it has multiple columns with differente data and one specific calculated column that populates a drop down with the positions that are currently "Open" for hiring in List 2.
- List 2: Contains all positions that have been opened for hiring, some are "Open" and some others are "Closed" becuase the positions have already been covered.
The problem is that if a candidate from List 1 had an Open position selected from the drop down but later on the position was Closed in List 2, the candidate form in List 1 gets bugged and cannot be opened.
I hope I have explained this well enough. I would really appreciatte some help here on how to work around this.