Random cell return using vlookup formula

[mattbeaves]

Hi


I have been designing a school report spreadsheet. I am looking to randomly select from a table array using vlookup. Basically at the moment when i enter a number in one worksheet it looks up from another worksheet, finds the name definition and returns the cell next to the number. I would like it to randomly select and return text from 5 or 6 cells next to the number so children don't get the same comment if they get the same effort grade. I hope the makes sense.
Formula so far
=IF(LEN(VLOOKUP(K69,Y6Cricket,2,0))=0,"",VLOOKUP(K 69,Y6Cricket,2,0))
Thanks
Matt

[ArviLaanemets]

=IF(ISERROR(VLOOKUP(K69,Y6Cricket,1,0)),"",VLOOKUP (K69,Y6Cricket,1+RANDBETWEEN(1,6),0))

[mattbeaves]

Thank you for your.

This works perfectly apart from one thing, I am trying to use this formula for 3 different column look ups on the same worksheet. Once I have entered the formula in the 3 coloumns, when changing the vlookup data in one column it changes all 3.

Thoughts?

Matt

[ArviLaanemets]

Are you meaning, that when you change some value in your table, then all comments are recalculated? This is an Excel feature. When you have automatic calculation on, then any change in any cell in any open workbook forces all formulas in all open workbooks to be recalculated. To check this, type something into any cell outside your table - you see that all comments are renewed.

You can set formula calculation to manual, but it doesn't help anyway - then no comments are recalculated at all, even the one for which you changed data. And manual calculation through F9 recalculates all comments again.

The only easy way to get random comments which don't change later, is through VBA procedure. You have to write the procedure, which has to check the active cell, to dermine, is this cell the one where a comment belongs, to read all nessessary parameters from your table, to calculate a random comment, and then to write it into active cell.

Not easy (a lot of manual work) way: have a separate sheet/workbook to calculate comments, and then copy-paste them into your table.