Permutations without repetition using options in categories

I've taken your file and removed the blank columns, and then inserted a new row 1 and a few more blank rows below your data so that you can add or remove items from rows 3 to 13 to give you some flexibility, then I put this formula in B1:

=COUNTA(B3:B13)

which can be copied across to F1 to return the number of options in each category. This formula in A1:

=PRODUCT(B1:F1)

tells you how many combinations you can expect (i.e. how many rows you need to copy the formulae down, before they start to repeat). Starting with F16, I used this formula:

=INDEX(F$3:F$13,MOD(ROWS($1:1)-1,F$1)+1)

and if you copy this down a few rows you will see that it returns the items from the range F3:F13, but as there are only 5 of them then it returns the first one again after the fifth one, and so on down the column. Notice that 1 is subtracted from the ROWS($1:1) term (resulting in zero), and the MOD term will thus return the values 0, 1, 2, 3, 4 then 0 again as the value of the ROWS term increases, so the 1 needs to be added back in to give the elements of the array F3:F13 that you want to return (i.e. elements 1, 2, 3, 4, and 5). I used this formula in E16:

=INDEX(E$3:E$13,MOD(INT((ROWS($1:1)-1)/PRODUCT(F$1:$F$1)),E$1)+1)

and if you copy this down a few rows you will see that it returns the first item from E3:E13 for as many times as there are items in F3:F13 (i.e. 5 times), and then starts to return the second item from E3:E13, and so on. Here the ROWS($1:1)-1 term is divided by F1, and then we take the integer value, so there will be 5 zeros, then 5 ones, and so on. These are then passed in turn to the MOD function, which returns the remainder after division, and then by adding the 1 back in we will get a series of ones, then twos, then threes etc., before wrapping back to zeroes after the number of times given by E1. Strictly speaking, the /PRODUCT(F$1:$F$1) term is not needed in this particular formula, as it could be replaced by /F$1, but it makes it easier for the other formulae, as the formula in E16 can just be copied into D16, C16 and B16, and then all the formulae in B16:F16 can be copied down for the number of rows given in cell A1 (i.e. 5250 rows with this set up).

Hopefully you will find this approach a bit easier to follow (and it doesn't use volatile functions).

Hope this helps.

Pete