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