='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40

Gives me #ref!

I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
Want it to automaticly Take date value from A2 - 1 year and put it in
the path for the CAF-Jan.xls file.

Sry removed previous post so I could post in both groups

Jerry W. Lewis wrote:
See answer to your previous post. Repeating a question after 18 minutes
does nothing to spead a reply.

Jerry

AMDGUY [MCP] wrote:

='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40

Gives me #ref!

I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
Want it to automaticly Take date value from A2 - 1 year and put it in
the path for the CAF-Jan.xls file.

See answer to your previous post. Repeating a question after 18 minutes
does nothing to spead a reply.

Jerry

AMDGUY [MCP] wrote:

='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40

Gives me #ref!

I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
Want it to automaticly Take date value from A2 - 1 year and put it in
the path for the CAF-Jan.xls file.

Also I can not figure out how to use the indirect() function
Could you give me an example base on the question?

Jerry W. Lewis wrote:
See answer to your previous post. Repeating a question after 18 minutes
does nothing to spead a reply.

Jerry

AMDGUY [MCP] wrote:

='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40

Gives me #ref!

I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this changes.
Want it to automaticly Take date value from A2 - 1 year and put it in
the path for the CAF-Jan.xls file.

=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
0")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"AMDGUY [MCP]" wrote in message
...
Also I can not figure out how to use the indirect() function
Could you give me an example base on the question?

Jerry W. Lewis wrote:
See answer to your previous post. Repeating a question after 18 minutes
does nothing to spead a reply.

Jerry

AMDGUY [MCP] wrote:

='I:\Shared\N.C.F\&text(A2-365,"YYYY")\[CAF-JAN.xls]MACH'!$C$40

Gives me #ref!

I:\Shared\N.C.F\2004 has previous years CAF-JAN. Every year this
changes.
Want it to automaticly Take date value from A2 - 1 year and put it in
the path for the CAF-Jan.xls file.

I still get the #REF! with that

Bob Phillips wrote:
=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
0")

You need to have the other workbook open when using indirect


Regards,

Peo Sjoblom

"AMDGUY [MCP]" wrote:

I still get the #REF! with that

Bob Phillips wrote:
=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
0")

I did have the other one open.

do you know if there is another way that you dont have to have the
workbook open?

Peo Sjoblom wrote:
You need to have the other workbook open when using indirect


Regards,

Peo Sjoblom

"AMDGUY [MCP]" wrote:


I still get the #REF! with that

Bob Phillips wrote:

=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
0")

It works if the other workbook is open but then you don't need the path, for
possible workarounds see

http://makeashorterlink.com/?F2993260A


Regards,

Peo Sjoblom

"AMDGUY [MCP]" wrote:

I did have the other one open.

do you know if there is another way that you dont have to have the
workbook open?

Peo Sjoblom wrote:
You need to have the other workbook open when using indirect


Regards,

Peo Sjoblom

"AMDGUY [MCP]" wrote:


I still get the #REF! with that

Bob Phillips wrote:

=INDIRECT("='I:\Shared\N.C.F\"&TEXT(A2-365,"YYYY")&"\[CAF-JAN.xls]MACH'!$C$4
0")