reference different workbook sheet using a cell value

[kell22]

Hi,

I have 2 workbooks one called "Overview" and one called "Dept 1". Each workbook has 12 sheets for the months of the year.


What I want to do is in the "Overview" workbook sheet "January", reference a cell "N$32" in the "Dept 1" workbook sheet "January". I did this using the formula:

='[Dept 1.xlsx]January'!N$32

I would like to develop this further so that I can substitute the sheet name for a value in cell "I5" - I am not sure if it is a problem that the value in I5 is generated using a formula to. this way the Overview February sheet will automatically look in the Dept 1 February sheet.

I also need this to work when the "Dept 1" workbook is closed. I have had a look at indirect referencing but it kept returning an error, can anyone modify the formula to do what I want.

I hope that makes sense, it is 04:20 currently so quite possible that it doesn't

Thanks in advance!

 

[JayBurn]

=INDIRECT( "'[Dept 1]" & MONTH & "'!" & LOOKUP)

Name I5 as MONTH and name an empty cell LOOKUP with N32 in it.