Forum Discussion
Indirectly selecting row and column
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?
Joern,
=INDEX($A:$XFD,2,4)
4 Replies
Joern,
=INDEX($A:$XFD,2,4)
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.
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
