I have the following formula:

In workbook1 that references workbook2 having multiple worksheets (sheet1,sheet2,sheet3,etc....)

What I would like to do is reference a cell in workbook1 that contains the correct sheet number (ie. if I type "sheet2" in cell A1,workbook1, it will reference workbook2,sheet2)

Something like this:

where A1 = sheet2

Is this possible? I have seen the INDIRECT() function being thrown around but used in context of referencing the filepath location, not the sheet name after the file location.

Thanks in advanced,