I need to have a formula in cell L4 that references a cell in a workbook called "VarianceP09" in the tab "Week 1" cell location C44.

As is, this is easy enough with a direct reference. However, depending on the week we're in will dictate what tab should be referenced sucha s, if we're in Week 2 then reference "Week 2" and so on. Also, if I use a "INDIRECT" formula then I get a #REF becuase the destination workbook will not be open.

Even worse, if we're in P10, P11, or any other period then it needs to reference that file folder.

I currently have the current period in cell Z4.

Help, please

Hi,

Sadly, INDIRECT doesn't work with closed workbooks, as you've found out.
Happily, there are ways around this.

Have a look at the below link for some further information:
http://www.dailydoseofexcel.com/arch...sed-workbooks/

It's not an instant solution for you, but without an example workbook and full file paths, nobody will be able to provide you with that, but it will get you on the right path with a little work.

Hope it's of help.

S.