Hello,
I'm trying to build a Schedule Generator using recursive Lambda based on RANDBETWEEN function.
Basically, what it needs to do is :
1: Generate a random date using RANDBETWEEN based on 2 other cells which includes start and end of the month.
2: Lookup A2#'s shift on that day, if it's not "OFF", set the cell's value to that date. If it's off, generate another date.
This is my messy formula. Of course it does not work because I still am not sure how recursive lambdas work. Also, I believe there is a logical error in the formula.
=LAMBDA(f,l,counter,LET(randomd,IF(counter>70,"",RandomDate(RANDBETWEEN(f,l),counter+1)),shiftlookup,TEXT(XLOOKUP(a2#,Names,XLOOKUP(randomd,Dates,Schedule!$B$3:$AE$67)),"hh:mm"),IF((shiftlookup="08:00")+(shiftlookup="16:00")+(shiftlookup="18:00"),shiftlookup,randomd+1)))
As of now, this Lambda doesn't let me call it using cell references. The exit condition of the loop needs to be
IF(A2#="","",Lambda)
I'm not sure if I set it up right.
I'm attaching a sample file for you to see what I'm working on.
Note: There is a simple macro embedded to calculate the sheet but I saved it as a macro-free workbook.
I'd really appreciate your help.
