M
Minitman
Greetings,
I am running Office 2003 on an XP box.
I have a sheet that is set up for printing that is linked to
a cell in another sheet or another workbook (with OFFSET's to capture
all of the date per record) depending on a set of conditions. I
posted this else where but was un able to get an answer (got a few
replies, tho).
here is the formula that is working but only in the 2000-01.xls
workbook:
=IF($K4<0,OFFSET('[1999-12.xls]Daily'!$D$41,42*(DAY($B4)-1)+2-2*ROW($A$1),0),OFFSET(Daily!$D$41,42*$K4+2-2*ROW($A$1),0))
K4 is on Mon of the first week in which the 1st of the month shows up.
This formula as it is written shows K4 to have the date of Dec 27,
1999. In an attempt to make this formula more universal, I came up
with these two possibilities, which Excel does not even recognize and
errors out!!!
="'["&YEAR(B4)&"-"&MONTH(B4)&".xls]Daily'!$D$41"
="'["&TEXT(B4,"yyyy-mm")&".xls]Daily'!$D$41"
These both show up as '[1999-12.xls]Daily'!D41, but they do not work
inside of the IF statement at the top of this posting!!
This last formula displays the correct contents of the referenced cell
='[1999-12.xls]Daily'!D41
If anyone could tale a look at these formulas and share any ideas as
to how they might be either tweaked or rewritten, I would really
appreciate it.
TIA
-Minitman
I am running Office 2003 on an XP box.
I have a sheet that is set up for printing that is linked to
a cell in another sheet or another workbook (with OFFSET's to capture
all of the date per record) depending on a set of conditions. I
posted this else where but was un able to get an answer (got a few
replies, tho).
here is the formula that is working but only in the 2000-01.xls
workbook:
=IF($K4<0,OFFSET('[1999-12.xls]Daily'!$D$41,42*(DAY($B4)-1)+2-2*ROW($A$1),0),OFFSET(Daily!$D$41,42*$K4+2-2*ROW($A$1),0))
K4 is on Mon of the first week in which the 1st of the month shows up.
This formula as it is written shows K4 to have the date of Dec 27,
1999. In an attempt to make this formula more universal, I came up
with these two possibilities, which Excel does not even recognize and
errors out!!!
="'["&YEAR(B4)&"-"&MONTH(B4)&".xls]Daily'!$D$41"
="'["&TEXT(B4,"yyyy-mm")&".xls]Daily'!$D$41"
These both show up as '[1999-12.xls]Daily'!D41, but they do not work
inside of the IF statement at the top of this posting!!
This last formula displays the correct contents of the referenced cell
='[1999-12.xls]Daily'!D41
If anyone could tale a look at these formulas and share any ideas as
to how they might be either tweaked or rewritten, I would really
appreciate it.
TIA
-Minitman