Add value from another cell into formula

[adput]

I'm using this formula to get the value of a cell from a sheet in another workbook:

=[workbook2.xlsx]sheet1!A1

I have a second formula that gets the name of the sheet itself:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

I want to change the first formula so it automatically gets the name of the sheet from the second formula.

Eg; if the second formula for the sheet name is in C1

=[workbook2.xlsx]C1!A1 - which doesn't work - it's probably (hopefully) really simple?

 

[Special-K99]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

works but the sheet has to be saved first (I believe).

 

[adput]

Hi - how do I it with the first formula?

 

[Special-K99]

Try

=INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)&"!A1")

 

[adput]

Really appreciate the help - I'm a little confused now though

How do I use the formula to find a value in a cell in another workbook?

 

[Teeroy]

Wouldn't that lead to a circular reference?