Random numbers - an added twist

[secondtry]

I previously received the following formula from pgc to give me a column of random numbers from 1 to 99 without duplicates and it worked great:

SMALL(IF(COUNTIF($A$1:A1,ROW($1:$99))<>1,ROW($1:$99)),1+INT(RAND()*(99-ROW()+ROW($B$2))))
Holding CTRL+SHIFT+ENTER since it is an array

After putting it to use I began to wonder if it could be tweaked so that the random number would only appear if the cell to the right was not blank. I haven't been able to make anything work. Any ideas?

 

[barry houdini]

Try this in A2 copied down, should give a blank if cell to the right (in column B) is blank

=IF(B2="",SMALL(IF(COUNTIF($A$1:A1,ROW($1:$99))<>1,ROW($1:$99)),1+INT(RAND()*(99-ROW()+COUNTIF($A$1:A1,"")+ROW($A$2)))),"")

confirmed with CTRL+SHIFT+ENTER

note: A1 should not be blank

 

[secondtry]

That worked like a charm. Within the formula, can I also change the range of random numbers that I want to appear? That is to say, if I want to apply the same formula but have the option of altering the range of numbers anywhere between 1 and 99, what can I do? When I tried replacing the "99" as demonstrated below, I still had random numbers of greater than the chosen variable appearing.

Original formula:
IF(C5="","",SMALL(IF(COUNTIF($B$4:B4,ROW($4:$102))<>1,ROW($4:$102)),1+INT(RAND()*(99-ROW()+COUNTIF($B$4:B4,"")+ROW($B$5)))))

 

[barry houdini]

you need to replace all three 99s, you haven't changed the last one