Excel Question....Random word?

  tnelis 12:36 24 Oct 2009
Locked

Excel Question....Random word?
Please can someone tell me how I can have a RANDOM word pop up in a sentence.
Example: The cook says * is on the menu.
So * can be:
A1 parrot
A2 pizza
A3 lettuce

A1-A3 are all on sheet1
The sentence is on sheet2

Will you please write the exact wording for me, thanks?

  VoG II 12:55 24 Oct 2009

Try

="The cook says " &INDIRECT("'Sheet1'!A"&INT(RAND()*3)+1)&" is on the menu"

  tnelis 13:09 24 Oct 2009

I am getting Err:502

Is there something I'm doing wrong. What should I check?

  VoG II 13:11 24 Oct 2009

Make sure that you have the exact formula. Select the formula above and press CTRL + C to copy it then click in a cell in Excel and press CTRL + V to paste it in.

  tnelis 13:18 24 Oct 2009

sorry, I still getting the error.

  VoG II 13:32 24 Oct 2009

I have uploaded a demo click here

  tnelis 14:24 24 Oct 2009

Thanks VoG, but I'm still getting the error...perhaps it's my copy of Open Office Calc that is not set up properly...any settings I should check on?

  VoG II 14:30 24 Oct 2009

So it isn't Excel!!!

Sorry, I don't know how to do that in Calc. I suspect that you will need to look for the equivalent function to INDIRECT, if one exists.

  VoG II 15:02 24 Oct 2009

Maybe it should be

="The cook says " &INDIRECT("'Sheet1'.A"&INT(RAND()*3)+1)&" is on the menu"

(. instead of !)

  tnelis 15:14 24 Oct 2009

Great. Works now thanks. One more question. What do I click to "refresh" the sheet, to get another RAND?

  tnelis 15:29 24 Oct 2009

Quick explanation...I am trying to design a "creativity" spreadsheet...which goes..."design a (bicycle) for (doctors)......or design a (bank) for (chilren)...so it would be nice to hit refresh to get the next RAND.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?