Referencing an External Cell

[Troy1973]

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

As is, this is easy enough with a direct reference. However, dependin
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
"INDIRECT" formula then I get a #REF becuase the destination workboo
will not be open.

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

I currently have the current period in cell Z4.

Help, pleas

 

[Spencer101]

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

As is, this is easy enough with a direct reference. However, dependin
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
"INDIRECT" formula then I get a #REF becuase the destination workboo
will not be open.

Even worse, if we're in P10, P11, or any other period then it needs t
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 foun
out.
Happily, there are ways around this.

Have a look at the below link for some further information:
http://tinyurl.com/8ndjqeu

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

Hope it's of help.

S