You can reference other cells, including cells in another workbook with the INDIRECT function.
The limitation is that the other workbook must be open.
INDIRECT requires an address, in your case a full
The address can be created with the ADDRESS function
Part of the ADDRESS function is the worksheet name.
If your data has a list of available worksheets, then you can lookup the worksheet name from the list based on the value in another cell.
For example I created 3 workbooks
A.xls, B.xls and C.xls
In cells A1 to A3 I put the workbook and sheet name of the 3 workbooks.
In cell B1 is a number, in this case from 1 to 3
and in cell C1 is the following formula:
The offset function selects a value from cells A1,A2 or A3 based on the value in cell B1.
For this test I put
WS-A in cell A1 in A.xls,
WS-B in cell A1 in B.xls and
WS-C in cell A1 in C.xls
This is what it looks like:
A B C
1 [A.xls]Sheet1 3 WS-C
With the address function you can use other formulas to manipulate the cell being accessed in the other worksheet.
If you need to open the referenced workbook first, then I think that a macro will be required.
Also note that the INDIRECT function is a 'volatile' function, which AFAIK recalculates every time anything changes on your worksheet. A few formulas using INDIRECT is OK, but if you had a large number, this would start to slow down Excel.
Hope this is what you were looking for.