Variable Sheet/Workbook Reference

[marklancer]

I'll try my best to explain the situation and hopfully someone will have an answer.

I want to retrieve the value of a cell from another sheet and/or workbook based on the cell value specific cell.

For example in the function:
=[test.xls]Sheet1!A1

I would like to define either [test.xls] or Sheet1 using the value of a cell within the current workbook.

Thanks in advance.
-Mark

 

[lenze]

If A2 contains your file name and A4 contains your sheet number, use the following

=INDIRECT("["&$A$2&".xls]"&"Sheet"&$A$4&"!C3")

This will return the value in [Test]Sheet2!C3

See Example

Test.xls
	A	B	C	D
1	File Name
2	Test		5555
3	Sheet Num
4	2
...

HTH