Look in HELP for the INDIRECT() function

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"rsmith" wrote in message ...
|i would like to use the text values in cells as the sheet name reference in a
| lookup function
|
| on sheet 1 we have the following data in each cell
| a2 = 5
| b2 = 'sheet2 (text)
| c2 = 'a1:a10 (text)
| d2= CONCATENATE(b2,c2) (text)
|
| i would like to replace the formula =lookup(a2,sheet2!a1:a10,sheet2!c1:c10)
| with the formula that uses the values in cells b2 and c2
| =lookup(a2,d2,sheet2!c1:c10)
|
| What am i missing in order to make this possible?