1

I'm trying to build a Schedule Generator using recursive LABMDA based on RANDBETWEEN function.

Basically, what it needs to do:

  1. Generate a random date using RANDBETWEEN based on 2 other cells which include 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 am still 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.

ZygD
  • 22,092
  • 39
  • 79
  • 102
BBK
  • 11
  • 1
  • 1
    Welcome to SO and congrats on a thorough question. Though people could opt to download a file, it's recommended to provide markdown sample data both for security reasons **and** in case the link goes obsolete. It would be best to come up with a much simpler set of data that would still encompass whatever problem you are facing. Some screenshots could support your query. Happy to help if I don't have to download anything! – JvdV Apr 10 '22 at 20:52

0 Answers0