0

I would like to achieve the following:

  1. Extract a specified range based on user input in a cell using the arrayformula
  2. Shuffle/randomise that extracted range

I have tried several approaches such as a combination of sort and randarray.

However, those always refresh and therefore reshuffle as soon as I check a checkbox in that row which I also need to perform an action with conditional formatting.

Does anyone know how I could solve this challenge?

Essentially I need a randomisation method that doesn't refresh unless the user changes the input in a specified cell.

2
  • Use a script based solution instead of something based on spreadsheet functions. Sep 8, 2023 at 18:25
  • I would suggest you edit your question with a minimal reproducible example including the formula and some sample data in markdown. Whether script or non-script will be clearer with a better explanation of the problem space.
    – Blindspots
    Sep 8, 2023 at 19:44

1 Answer 1

1

Assuming the range to shuffle is in A2:C, put this formula in cell H2:

=let( 
  refresh, H1, 
  freeze_, lambda(_, _), 
  randomNumbers_, lambda(r, c, randarray(r, c)), 
  freeze_(randomNumbers_(999, 1)) 
)

...and this formula in cell J2:

=sort( 
  filter(A2:C, len(B2:B)), 
  filter(H2:H, len(B2:B)), 
  true 
)

To reshuffle the results, select cell H1, choose Insert > Checkbox, and toggle the checkbox.

You can hide column H if it gets in the way.

1
  • That's what I'm thinking too.
    – Blindspots
    Sep 11, 2023 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.