Short version

This code returns a cell reference correctly:

Formula: copy to clipboard 
=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))


And this hard-code OFFSET works as expected: ($A$4 was returned by the formula above)

Formula: copy to clipboard 
=OFFSET($A$4,1,2,1,1)


However, when I combine the two code snippets,

Formula: copy to clipboard 


=OFFSET(=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0))),1,2,1,1)


I receive a dialog box that says [ " the formula you have entered contains an error " ]

Any suggestions/direction would be much appreciated!!

-----------------------------------
Long Version / Details

My data consists of a set of numbers for each day of the week (M-F) and they are populated in rows.

Here's some sample data-

HTML Code: 
Each week I insert another 5 rows and manually populate these values.
All of this is on sheet1

Sheet 2 pulls some values from the 5 rows (M-F).
I would like to have sheet 2 be able to determine the most recent week and return data from the correct set of 5 rows.
I populated a cell in COL A with the ending date, for example 09/21/2012 -- one row above the daily data rows
And then used a function to find the cell reference of the most recent(largest) Date in COL A.

This formula returns $A$4 (before A10 was populated..)
=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))
When I populate A10 with 09/28/2012 it returns $A$10. There is surely a less verbose way of doing this, but it seems to work..

When I *hard-code* a reference in the OFFSET function, it appears to return the correct value. Example, these both return "45", which is the value in C5 (again, without A10's new date)

=OFFSET(A4,1,2,1,1)
=OFFSET($A$4,1,2,1,1)
However, when I substitute the hard-coded REFERENCE parm ("$A$4") with the formula that returns "$a$4 like this:
Formula: copy to clipboard 


=OFFSET(=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0))),1,2,1,1)


I receive a dialog box that says [ " the formula you have entered contains an error " ]. I don't know anything about Excel (perl programmer..)

How do I trace down the incompatibility (ies)?

TIA,
Mark