Why does not Offset work with Address?

[Sundlof]

Hi,
This function gives me an error, But I am expecting it to work.

=OFFSET(ADDRESS(MATCH(TODAY();A:A;0);1);3;0;4)

I have dates in A column, and I want to use the cell for todays date in the column as a reference. The Address function works as intended and the Offset works, but no together.

 

[awoohaw]

I'm not sure what you're data looks like. But, I think the ADDRESS() function returns a string not a cell that can be used in calculations. Maybe you need to wrap it with INDIRECT()? (I don't think that will work either).

 

[Fluff]

As far as I know, you have to specify a cell reference for offset, not a formula.
Maybe

=OFFSET(A1;MATCH(TODAY();A:A;0)+3;0;4)

 

[ExceLoki]

as awoohaw said, try this

=OFFSET(INDIRECT(ADDRESS(MATCH(TODAY(),A:A,0),1)),3,0,4)

 

[Sundlof]

Thanks for you answers. I managed to get it to work with:

=OFFSET(INDIRECT(ADDRESS(MATCH(TODAY();$A:$A;0);1));(Trend!$F$2*-1);BC$11;(Trend!$AF$2+1))

That code puts out just what I want in the sheet.

But! It does not work when I put it in a name reference and then add it to a graph. Then Excel crashes actually! Do you think this has to do with Indirect or something else in my code?

 

[Sundlof]

I managed to solve the last issue but using CELL, for some reason.

=OFFSET(INDIRECT(CELL("address";INDEX(TrendData!$A:$A;MATCH(TODAY();TrendData!$A:$A;0))));(Trend!$F$2*-1);TrendData!$BC$11;(Trend!$AF$2+1))

Thanks for the help!