Posting a date based on an event

Very nice. This may do the trick if I can get on the same page with you.  So here is what is going on.  I have a sheet called "BO Schedule" using 5 columns. Column "B" is a column labeled "BO BEGIN DATE".  This column contains the following formula:  =LET(firstday, DATE(B2, 1, 1), firstmonday, firstday+7-WEEKDAY(firstday, 12), weeks, ROUNDUP((EDATE(firstday, 12)-firstmonday)/7, 0), SEQUENCE(weeks, , firstmonday, 7)). This generates a column of dates all of which start on a Monday.  Formatted as: Monday, January 6, 2025.  Cell "B1" is labeled as "BO YEAR" & cell "B2" is for the year.  In this case cell "B2" = 2025.  This generates me a column of Monday dates for year 2025 starting with Monday, January 6, 2025 thru Monday, December 29, 2025. 52 Mondays. 

Column "C" cell "C3" is labeled as "MALE BO". Starting in cell "C4", This column is where the Randomly selected Male persons are pasted to from column "E". Column "E" has this formula in it: 

=LET( n, FILTER(Room_Roster[NAME], (Room_Roster[NAME]>" ")*(Room_Roster[GNDR]=$E$3)*(Room_Roster[POSITION]<>Lists!L9)*(Room_Roster[Occ Status]="O")*(Room_Roster[Duty Completed Date]<=EDATE(TODAY(),-Lists!L7))*(Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), -Lists!L5))), count, ROWS(n), SORTBY(n, RANDARRAY(count))).

Column "E" cell "E3" has an "M" in it.  M = Male

Column "D" cell "D3" is labeled as  "FEMALE BO".  Starting in cell "D4", This column is where the Randomly selected Female persons are pasted to from column "F". Column "F" has this formula in it:

=LET( n, FILTER(Room_Roster[NAME], (Room_Roster[NAME]>" ")*(Room_Roster[GNDR]=$F$3)*(Room_Roster[POSITION]<>Lists!L9)*(Room_Roster[Occ Status]="O")*(Room_Roster[Duty Completed Date]<=EDATE(TODAY(),Lists!L7))*(Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), -Lists!L5))), count, ROWS(n), SORTBY(n, RANDARRAY(count))). 

Column "F" cell "F3" has an "F" in it.  F = Female

The desire here is to post the date this paste takes place, IE: the "Selected Date", into the "Selected Date" column, column "AE" starting in cell "AE3" on the ALL TAB sheet, applicable to the matching individual that was selected.  The ALL TAB is named "Room_Roster" and the [  ] depict the column labels accordingly.

The formulas above are making selections based on criteria with in the formula and copied and pasted.  

On the ALL TAB, within the Room_Roster columns, column "AI"  is receiving the "BO Schedule" "Begin Date" using the following formula: 

=IF([@NAME]="", "", XLOOKUP([@NAME], IF([@GNDR]="M", 'BO Schedule'!$C$4:$C$108, 'BO Schedule'!$D$4:$D$108), 'BO Schedule'!$B$4:$B$108, ""))

I am just trying to also post the Date into the "Selected Date" column, the date the Begin Date is posted against the individual who was selected.

Lastly, the "Lists" mentioned in the formulas, refers to the "Lists Tab" which has the info needed for "Lists!L9" & "Lists!L7".  Currently, "Lists!L7" is "12" and "Lists!L9" is "SG".  Currently, "Lists!L5" is "6".

This may be more than you need but I wanted to be clear on what I need when it comes to the posting of the "Selected Date" and the trigger causing the date to be posted.

Thank you so much for your reply and trying to help me out.  I hope you can assist me with making this happen.

V/r,

Carl W. Ladd