Indirect Help

[will2learn]

I have two workbooks, the second contains many sheets. In the first i need to enter a sheetname in cell D2.

In cell D6 I have this formula ='[NEW Format4.XLS]Summary'!$B$12 that looks on a sheet called Summary and gets the data from cell B12

I know I need to use indirect but cannot fathom out so that it picks up the sheet name from cell D2.

Can anyone help as I've tried varios ways but cannot fathom it out/

 

[VoG]

Try

=INDIRECT("'[NEW Format4.XLS]"&D2&"'!$B$12")