Thanks Chris but I don't think VLOOKUP helps. I was thinking something like:
=INDEX(Sheet 2!A$2:D$6,RANDBETWEEN(1,COUNTA(A$2:D$6)))
This formula returns a random value but the values are repeated.
Assuming Row 1 contains headings (on both Sheet1 and Sheet2):
Sheet2!D2 to Sheet2!D6 contain concatenated values that look like this:
Red A1
Red A2
Red A3
Red A4
Red A5
Sheet1!A2 to Sheet1!A6 contain names as follows:
John
Jane
Frank
Mary
George
What I am looking for is a formula to put into cells Sheet1!B2 to Sheet1!B6.
Formula needs to randomly select a ticket number from cells Sheet2!D2 to
Sheet2!D6. Value needs to be unique and cannot be repeated (error message
needs to be displayed maybe if not enough tickets?)

--
Regards
Matt


"Chris Bode" wrote:


Well, I suggest you to use vlookup because it is a convenient way to
make a reference between sheets€¦even workbooks
1.select a cell in column (say B1) in sheet1 and enter following
formula
=VLOOKUP(A1,Sheet2!A1:D3,4,FALSE)



Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode