[Solved] How to reference to cell relative to this cell?

[galaxy5]

Hi! I'm trying to implement dependent drop down list, in which the set of options depends on selection in the left relative cell.
In the other topic I was pointed to formula: INDEX(Cities;MATCH(A7;Countries;0)). But in this formula cell "A7" is hardcoded.
How to replace the hardcoded address with an expression which returns the address of a cell relative to "this" one?
I know about "OFFSET" function, but I have no idea how to substitute "this" cell into it.

[galaxy5]

Fortunately, I was able to get the reference to the relative cell using "INDIRECT(ADDRESS(ROW();COLUMN()-1)",
but now there is some wrong values ("#VALUE!") in the resulting list (cell C2).

[galaxy5]

I'm confused a lot more, because the count of items of the resulting range is right: "COUNTIF(INDEX(goods;0;MATCH(INDIRECT(ADDRESS(ROW();COLUMN()-1));category;0));".*")"...

[Villeroy]

Don't waste your time. Do yourself a favour and learn the most trivial basics about the tool your try to use. A7 is a relative reference. $A$7 is an absolute reference. This is the most fundamental thing in all spreadsheets since Visicalc of 1979.

[galaxy5]

How it explains appearance of "#VALUE!" options in drop down list in C2?
What I'm trying to do is to select product from category, in each row. Category cell is relative of product cell and is placed from left of it.
That's why I select subrange of 'goods': INDEX(goods;0;MATCH(INDIRECT(ADDRESS(ROW();COLUMN()-1));category;0)).
What I do wrong?

[Villeroy]

#VALUE has nothing to do with the reference itself. It means that some referenced cell has an unexpected value, e.g. text instead of number. The whole thing with address(row(),column()) is obsolete.

[galaxy5]

1. What is not obsolete?
2. If they are obsolete, then why it is not marked in user's manual as obsolete?
3. Why these functions work well being typed separately? I mean "MATCH(INDIRECT(ADDRESS(ROW();COLUMN()-1));category;0)" returns correct value; "INDEX(goods;0;1)" being typed in 'Validity' form returns correct value too. All function arguments seems to be appropriate according to manual.
If a software behaves not as described in manual I consider it as bug. What I do/say wrong?

[Villeroy]

The use of these functions is obsolete for relative addressing. There are other use cases for ADDRESS and INDIRECT.
How can I tell without seeing your document? Why don't you attach a sample document?

[galaxy5]

I have attached the document in my 2nd message of this topic, and I even pointed to cell C2 as a problem.
But now the problem is solved. It was definitely a LO bug, because in LO 5.0.1.2 the same document works OK.

[Villeroy]

=MATCH(INDIRECT(ADDRESS(ROW();COLUMN()-1));category;0) returns 3 which is correct.
=MATCH($B3;category;0) is equivalent.

=COUNTIF(INDEX(goods;0;MATCH($B3;category;0)) is missing a second argument. COUNTIF(criterion;range)

[galaxy5]

=MATCH(INDIRECT(ADDRESS(ROW();COLUMN()-1));category;0) returns 3 which is correct.
=MATCH($B3;category;0) is equivalent.

This 'equivalent' seems to be error-prone. What if I put my formula into a named expression and then apply it on another page? In this case I forced to check, what cell is selected when editing or copying the formula and may be forced to refuse from absolute addressing to column ($B).

[Villeroy]

Assuming you define
myMatch=MATCH($B3;category;0)
while the active cell is in row #3. Then you definded
myMatch=MATCH(Column_B|ThisRow;category;0)

Absolute and relative references are always relative to the currently active cell when you
- enter a formula into a cell
- define a conditional format
- define a named expression
- define a validation rule
- wherever $A$1 and A1 makes the difference