Want to generate random text from sheet in multiple cells

Originally Posted by crbenn01

. . . I Inputed:

=CHOOSE(RANDBETWEEN(1,57),goals!B4, . . . ,goals!B17,goals!C4, . . . ,goals!D4, . . . ,goals!D17,goals!E4, . . . ,goals!E17)

Note: B4:E17 is 14 rows x 4 columns, so 56 cells. Do you want errors for the 1-in-57 times RANDBETWEEN returns 57? FWIW, you could get the same effect with the shorter

=IF(RANDBETWEEN(1,57)=57,#VALUE!,INDEX(goals!B4:E17,RANDBETWEEN(1,14),RANDBETWEEN(1,4))

this into cell C and copied it to cell B and when I copied it show "0" and when I copied it again it showed "0" in the next cell?

FWIW, you're using RELATIVE references, so if you copy from a cell in col C to a cell in col B in the same row, the formula in col B would refer to goals!A4:D17. If goals!A4:A17 were blank or evaluated to 0, there'd be a 14/57 chance 0 would be the correct return value. Are you sure you don't want to use goals!$B$4,goals!$B$5,...,goals!$E$17 ?

Your 2nd formula may indicate an error. Do you see a circular reference indicator in Excel's status bar at the bottom of the Excel application window? OTOH, are there any blank cells in Names!A:A above the last nonblank cell? Extreme example: if Names!A1:A20 were all blank and Names!A21:A36 were the only nonblank cells, your formula would sample in Names!A1:A16, so return 0.

Given only your formulas but without seeing the cells/ranges your formulas are referencing, it's impossible to diagnose the problem other than educated guesses that relative rather than absolute references, circular references, or gaps in data could be the causes.

Hi, so i attached the file I'm using. I want the goal to correspond with the drop down. For example:

If MTO is selected then a random goal from goals!d4:goals!e17 are selected
If POS is selected then a random goal from goals!b4:goals!d17 are selected...

Originally Posted by crbenn01

. . .
If MTO is selected then a random goal from goals!d4:goals!e17 are selected
If POS is selected then a random goal from goals!b4:goals!d17 are selected...

If you're printing the worksheet named February 7th, 2020, then you could use random sampling in Excel, and those printed exhibits would have fixed results. In Excel itself, formulas calling RANDBETWEEN will recalculate any time anything triggers recalculation. I'll assume this isn't a problem.

I believe you mean row 11 cells in the February 7th, 2020 worksheet have the drop-down which contains MTO, POS, etc., and you want to pull in random entries in rows 13 and below based on the entry in that column in row 11, try

C21: =INDEX(goals!$B$4:$E$17,RANDBETWEEN(1,14),RANDBETWEEN(1,4))

That said, if you want no repeats in C21 and C29, you need to use supporting cells.

C101: =RANDBETWEEN(1,14)
C102: =RANDBETWEEN(1,4)

C104: =RANDBETWEEN(1,13)
C105: =RANDBETWEEN(1,3)

C107: =C104+(C104>=C101)
C108: =C105+(C105>=C102)

Then

C21: =INDEX(goals!$B$4:$E$17,C101,C102)

C29: =INDEX(goals!$B$4:$E$17,C107,C108)

Note: goals in rows 21 and 29 will always come from different columns and different rows in goals!B4:E17.