OFFSET Question

[msutfin1]

Short version

This code returns a cell reference correctly:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

However, when I combine the two code snippets,

Please Login or Register  to view this content.

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-

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..)

Please Login or Register  to view this content.

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)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

However, when I substitute the hard-coded REFERENCE parm ("$A$4") with the formula that returns "$a$4 like this:

Please Login or Register  to view this content.

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

[FDibbins]

try taking the = out before "cell"

[msutfin1]

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

Still returns "The formula you typed returns an error". Thanks for the help.. any other suggestions welcome!

[FDibbins]

ok, i didnt test it, sorry, just saw the =cell. what are you trying to achieve with this? if i leave off the =offset() and just use =CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0),1)) it gives me $A$4.

maybe upload a sample workbook showing what you have, what you expect, and how you arrive at that

[msutfin1]

I have uploaded a small workbook with some data and formulas. (working and non-working..)

Marvelous that you have a moment to look at it!

Thanks

[msutfin1]

Here's the sample data workbook

[Ace_XL]

Try..

Please Login or Register  to view this content.

or without the height and width reference

Please Login or Register  to view this content.