Indirectly selecting row and column

[Joern Bernhardt]

The following is a trivial example, which works quite well. I'm actually calculating the row/column based on other values, but this should already show my problem:

=INDIRECT("R2C4";FALSE)

This would result in the value that resides in the cell D2. Perfect.

BUT when my colleague opens the Worksheet in their German locale/language version of Excel, that formula fails because of a reference error.

Instead of "R2C4", the German Excel version needs "Z2S4" (Z = Zeile instead of R=Row, S = Spalte instead of C=Column).

Is there a way to calculate the "R2C4" string or is there another function than INDIRECT to get a value of a cell which works across language versions?

[#2 · Accepted solution]

Joern,

=INDEX($A:$XFD,2,4)

[#3]

I used OFFSET(A1;1;3;1;1) for now. I'm still thinking about how fundamentally wrong it is, having to translate functions and coordinates.

[#4]

Joern,

the advantage of INDEX() over INDIRECT() and OFFSET() is that it is not volatile.

[#5]

@Joern Bernhardt,
It may sound ridiculous but giving it a try would not harm anyone.
Put the following UDF in a VBA Module:
------------------------
Function Laguage_ID()
     Laguage_ID = Application.LanguageSettings.LanguageID(msoLanguageIDInstall)
End Function
------------------------
and then use the following formula:
=INDIRECT(IF(Laguage_ID() <> 1031,"R2C4","Z2S4"),0)

Michael (Micky) Avidan