Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using R1C1 notation in different languages

Tags:

excel

I have built a spreadsheet which uses R1C1 notation in the formulas itself. I have succeeded in doing everything that needs doing without having to resort to macros or VBA (which require a different file format and that the user "enable macros" when opening the file, etc).

The only problem is that I have now noticed that while Excel translates all of its functions to the local language (I have to deal with English, Portuguese and perhaps Spanish), it does not deal with the string literals which are used for the R1C1 notation. For example, INDIRECT("R[1]C[1]") is translated in Portuguese to INDIRETO("R[1]C[1]") which would be fine were it not for the fact that Portuguese R1C1 is actually L1C1.

I assume that were I to use VBA this would not be a problem since VBA isn't translated, which I would take to mean that R1C1 is in fact R1C1 regardless of the client language. But is it possible to do this without having to resort to VBA?

like image 885
Wasabi Avatar asked Oct 02 '22 22:10

Wasabi


2 Answers

I came across the same issue, and here is how I fixed it

In a separate hidden sheet, I enter this formula in two cells:

=LEFT(ADDRESS(1;1;1;0);1)

=MID(ADDRESS(1;1;1;0);3;1)

This give me the two characters used for the R1C1 style reference, here "R" and "C". In French, it gives me "L" and "C".

I then use these characters to recompose indirect adresses, for instance with CONCATENATE

like image 98
galinette Avatar answered Oct 05 '22 13:10

galinette


Old thread but I bumped into the same problem. Another ones of the great shortcomings of formulas in Excel, especially considering that "Rangée" is a perfectly acceptible synonyme to "Ligne" in French and would allow to avoid this petty issue completely.

I think a smarter way to go on about it is to try to avoid R1C1 notation completely. One way, if you are trying to get a relative location with ROW() & COLUMN() (which was my case):

Instead of:

OFFSET(INDIRECT("R"&ROW()&"C"&COLUMN()),-2,-2)

which will fail in French (and no doubts other languages), try:

OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,-2)

Of course this might be more of a job depending how many of these are in your workbook (and if you know where they are or not). But that's more reliable than matching based on a separate table IMO.

like image 29
logicOnAbstractions Avatar answered Oct 05 '22 12:10

logicOnAbstractions