RANDOM from existing information

hi gary, try:
=INDIRECT("'"&CHOOSE(RANDBETWEEN(1,3),"Good","Bad","Ugly")&"'!E"&RANDBETWEEN(3+(ROW(D1)-1)*2,4+(ROW(D1)-1)*2))

In e8 of Main over view sheet use

Please Login or Register  to view this content.

Change the highlighted red portion to change cell reference numbers as per requirement

i might go down in history for 1 of the longest content here. first of all, your template isn't very formula friendly as it goes all over the place. that's why the formula Ace_XL provided would need you to manually change the formula in every different cells you copy to. i have managed to solve only up to manager 3. i'm afraid you have to manually change for the rest.
=INDIRECT("'"&CHOOSE(RANDBETWEEN(1,6),"Gerard Wallace","Kirsten McKay","Kirsty McGowan","Martin Miller","Stacey Cavanagh","Tony Nicol")&"'!"&CHOOSE(RANDBETWEEN(1,2),"O"&24+(ROWS(E$8:E8)-1)*16+(COLUMNS($E8:E8)-1)/4,"O"&25+(ROWS(E$8:E8)-1)*16+(COLUMNS($E8:E8)-1)/4))

first off, try putting this formula in the "MAIN OVERVIEW" sheet:
='Gerard Wallace'!O24
you'll realise the name of the sheet is surrounded by single quotes (occurs when there's a space in the sheet name), followed by an exclamation mark to separate the sheet name & cell reference, then the cell reference. so our objective is to create a random text that produce something like the above. but assuming we have done so & it returns us the text in double quotes as such, you'll realise it'll not work:
="'Gerard Wallace'!O24"
INDIRECT formula
because the above is simply a text. but if we use the INDIRECT formula, it will convert that to a cell reference:
=INDIRECT("'Gerard Wallace'!O24")

RANDBETWEEN
this is pretty straightforward. the number will refresh between the bottom & top numbers you specify.

CHOOSE
since we want to random between 6 sheet names, having a number is not enough. the CHOOSE formula has at least 2 arguments. first is the index number. the 2nd is the Value1. anything more is optional. let's try 3 arguments:
=CHOOSE(1,"A","B")
since my index number is 1, it'll return me the Value1, which i input as "A". if my index number is 2:
=CHOOSE(2,"A","B")
the result will be "B". so our index number here can be a random between 1 to 6 & the value1 to value6 can be the sheet names:
CHOOSE(RANDBETWEEN(1,6),"Gerard Wallace","Kirsten McKay","Kirsty McGowan","Martin Miller","Stacey Cavanagh","Tony Nicol")

the above formula will be combined in between the single quotes, the above formula & the exclamation mark . all we need now is randoming of 2 cell references. for cell E8, it will be O24 or O25. if we were to copy down 1 cell, it should add 16 to it & refer to O40 or O41. if i copy to cell M8, it should add 2 to it & refer to O26 or O27. so i can say:
=O24 + xxx + yyy
where xxx & yyy will be variables when copying to down or across.

ROWS
to make incremental numbers while copying down, we can use ROWS. this formula will take return you the total number of rows you range up. for eg
=ROWS(A1:A10)
this will return you 10, for 10 rows selected. ROWS(A11:A20) or ROWS(B5:B14) will return the same results. columns dont matter. since we want E8 's cell reference to remain in O24, xxx should be 0. so:
=ROWS(E$8:E8)-1
the first E8 is fixed at the row so copying downwards will change it to ROWS(E$8:E9) & return 2. we want it to add 16 & return O40, so we do a multiplication of 16:
=ROWS(E$8:E8)-1)*16

COLUMNS
similar to ROWS, COLUMNS return the total number of columns you range up. again, we want yyy to show 0 so that E8 will show O24. so:
=COLUMNS($E8:E8)-1
but copying this to M8 will be COLUMNS($E8:M8)-1, and this will return us "8". since we want it to only increase by 2 to show O26, we can divide it by 4:
=(COLUMNS($E8:E8)-1)/4

the exact same thing is done for O25.
_______________________________________________________________________________________________________________________
while writing this, i came up with another part that can now be used universally in the sheet, but i'm not going to re-write the above part. so i'm just adding this red portion inside:
=INDIRECT("'"&CHOOSE(RANDBETWEEN(1,6),"Gerard Wallace","Kirsten McKay","Kirsty McGowan","Martin Miller","Stacey Cavanagh","Tony Nicol")&"'!"&CHOOSE(RANDBETWEEN(1,2),"O"&24+(ROWS(E$8:E8)-1)*16+(COLUMNS($E8:E8)-1)/4-FLOOR(ROWS(E$8:E8)/20,1)*298,"O"&25+(ROWS(E$8:E8)-1)*16+(COLUMNS($E8:E8)-1)/4-FLOOR(ROWS(E$8:E8)/20,1)*298))

FLOOR
will round down the number to the nearest significance i specify. ROWS(E$8:E8)-1)*16 copied down to E27 will give me (ROWS(E$8:E27)-1)*16 & return a value of 304. i dont want to add 304 to O24, just 6 to make it O30. so i need to deduct it by 298. and this deduction should only happen when it reaches 20 rows down & above. so for E8, it will look like this & return me 0 since 1/20 is less than 1.
=FLOOR(1/20,1)
this continues for another 19 rows until it reaches:
=FLOOR(20/20,1)
so this gives me "1". and i simply multiply this by 298. the 1st part of course from 1 to 20 is replaced by the ROWS formula:
FLOOR(ROWS(E$8:E8)/20,1)*298

hope this REALLY helps.