Syntax To Link 2 Workbooks

[Minitman]

Greetings,

I need to modify this code snippet:

'[1999-12.xls]Daily'!D41

D41 on the Daily sheet (on all the workbooks in this series) is a
reference cell. All of the workbooks are number as dates in the
yyyy-mm format. For example: the workbook for the month of April in
2003 is called 2003-04.xls.

In the code snippet above, I need to make it more universal. Here are
two example of this:

="'["&YEAR(B4)&"-"&MONTH(B4)&".xls]Daily'!$D$41"

and

="'["&TEXT(B4,"yyyy-mm")&".xls]Daily'!$D$41"

The display of both of these look the same as the hard coded snippet
above if B4 is Dec 27,1999. The code snippet works, these other two
don't work at all. If I enter these into the IF statement as written,
Excel will not even let me enter them. If I remove the "s and &s, I
can enter them and get a #VALUE! error. GRRRRRRR

Anyone have any ideas?

TIA

-Minitman

 

[Guest]

The syntax and logic of what you have looks correct for both expressions so
it might be how you are using them in the statement. Please show the entire
IF statement where you are using this.

 

[Minitman]

No problem.

This is the entire formula in the receiving cell that returns the
contents of the correct cell on the 1999-12.xls workbook:

=IF(K4<0,OFFSET('[1999-12.xls]Daily'!D41,42*(DAY(B4)-1)+2-2*ROW($A$1),0),OFFSET(Daily!$D$41,42*K4+2-2*ROW($A$1),0))

If you would like a more complete explanation of what the code does, O
can do that also, just let me know.

So what do you think?

-Minitman

The syntax and logic of what you have looks correct for both expressions so
it might be how you are using them in the statement. Please show the entire
IF statement where you are using this.
--
- K Dales

Greetings,

I need to modify this code snippet:

'[1999-12.xls]Daily'!D41

D41 on the Daily sheet (on all the workbooks in this series) is a
reference cell. All of the workbooks are number as dates in the
yyyy-mm format. For example: the workbook for the month of April in
2003 is called 2003-04.xls.

In the code snippet above, I need to make it more universal. Here are
two example of this:

="'["&YEAR(B4)&"-"&MONTH(B4)&".xls]Daily'!$D$41"

and

="'["&TEXT(B4,"yyyy-mm")&".xls]Daily'!$D$41"

The display of both of these look the same as the hard coded snippet
above if B4 is Dec 27,1999. The code snippet works, these other two
don't work at all. If I enter these into the IF statement as written,
Excel will not even let me enter them. If I remove the "s and &s, I
can enter them and get a #VALUE! error. GRRRRRRR

Anyone have any ideas?

TIA

-Minitman