Calc OFFSET / CELL Incompatibiltiy

[rumple1001]

In my Calc sheet the formula:

CELL(“address”) correctly returns $C$10
OFFSET($C$11,0,1,1,1) correctly returns the value in D11
but
OFFSET(CELL(“address”),0,1,1,1) returns Err:504 and I cannot understand why?

The error does not seem to be due to the lack of a 2nd reference variable in CELL either e.g. CELL(“address”; b2). Indeed ADDRESS() doesn’t work as a nested function of OFFSET for me either.

I know I can use the current cell’s relative address but I specifically want to use the ‘current’ cell format CELL(“address”) because I want to import a csv file where Column P has exactly the same formula in every row but when evaluated on import, returns the correct values for that row. Just what OFFSET does so well but this time I need it to relate to the current cell but without stating its explicit address.

[karolus]

@rumple1001

returns Err:504 and I cannot understand why?

Its for the same reason as:

=C11     # returns the content of C11
="C11"  # returns literally »C11« 

you may need to use:

=OFFSET( INDIRECT( CELL("address")) ; 0 ; 1 )

but thats ugly double cast from reference to string and back!

[rumple1001]

Ah!! Thank you very much.

CELL() returns the address as text (which I should have worked out). You’d think the online manual would be more a bit more explicit though just by specifying:

“Returns the absolute address of the cell …as text”

Thanks for resolving my quandary