Hi,
In a cell I used a formula like =INDIRECT("R[-1]C",FALSE).
This works fine on an English version of Excel, but if you open the workbook
on a system where the language is set to French for instance, the formula
returns a #REF! error.
In French, the formula should be =INDIRECT("L[-1]C",FALSE), (i.e. L for
Ligne and C for Colonne).
On a Dutch system, Excel translates the *function* to
=INDIREKT("R[-1]C",FALSE) but the #REF! error remains because it should be
=INDIREKT("R[-1]K",FALSE) (i.e. R for Rij and K for Kolom).
On a Germans system or a Spanish system or ...(I can go on like that...),
the problem is similar.

Can anyone provide me with a reliable way to determine how the words "Row"
and "Column" are abbreviated on the current system where Excel is running so
I can use these characters in the INDIRECT function?

I searched this newsgroup and I found a zillion messages for the R1C1
reference style problem, but no one seems to have encountered this problem
(or maybe they found the solution themselves :-)

Any help appreciated.
-=Wim=-

"Wim SKW" wrote...
....
Can anyone provide me with a reliable way to determine how the words "Row"
and "Column" are abbreviated on the current system where Excel is running
so I can use these characters in the INDIRECT function?

No. Internationalization just doesn't work well with R1C1 indirect
addressing or the CELL function.

Since you're already using the volatile INDIRECT, no great harm changing to
the volatile OFFSET. For example, the following formulas in cell X99 are
equivalent.

=INDIRECT("R[-1]C",0)

=OFFSET(X99,-1,0)

but =X98 would be better still. Why are you using INDIRECT with R1C1
addressing? There may be more robust alternatives.

Hi Harlan,

"Harlan Grove" wrote:

No. Internationalization just doesn't work well with R1C1 indirect
addressing or the CELL function.

Since you're already using the volatile INDIRECT, no great harm changing to
the volatile OFFSET. For example, the following formulas in cell X99 are
equivalent.

=INDIRECT("R[-1]C",0)

=OFFSET(X99,-1,0)

Actually the formula used in the original post was not complete. It should
have been =INDIRECT("R[-1]C",FALSE) + 1
I use this to sequentially number the items in a list. When I would use =X98
+ 1 in cell X99 etc... and I would insert some rows, then the formulas below
the inserted rows would all be wrong. I solved this with the above formula.
The better way is indeed to use the OFFSET function.
Thanks for the suggestion!

but =X98 would be better still. Why are you using INDIRECT with R1C1
addressing? There may be more robust alternatives.

I have a named range "IsLocked" which refers to
=GET.CELL(14,INDIRECT("RC",FALSE)). I conditionally format cells with
=IsLocked to automatically set the background color of the cell to grey. The
unlocked cells remain white so the user has a visual clue as to which cells
he can modify.
Any workarounds for this one?
Btw, I use Excel 2002 SP3.
-=Wim=-

"Wim SKW" wrote...
....
I have a named range "IsLocked" which refers to
=GET.CELL(14,INDIRECT("RC",FALSE)). I conditionally format cells with
=IsLocked to automatically set the background color of the cell to grey.
The unlocked cells remain white so the user has a visual clue as to
which cells he can modify.
....

Finally a valid use for INDIRECT(ADDRESS(...)) - internationalization.

=GET.CELL(14,INDIRECT(ADDRESS(0,0,4,0),0))

Hi Harlan,

"Harlan Grove" wrote:

....
I have a named range "IsLocked" which refers to
=GET.CELL(14,INDIRECT("RC",FALSE)). I conditionally format cells with
=IsLocked to automatically set the background color of the cell to grey.
The unlocked cells remain white so the user has a visual clue as to
which cells he can modify.
....

Finally a valid use for INDIRECT(ADDRESS(...)) - internationalization.

=GET.CELL(14,INDIRECT(ADDRESS(0,0,4,0),0))

Brilliant!
The ADDRESS function did the trick.
I now defined two names: mR which refers to =LEFT(ADDRESS(0,0,4,NOW()*0),1)
and mC =RIGHT(ADDRESS(0,0,4,NOW()*0),1).
NOW()*0 always evaluates to 0 but it forces Excel to recalculate. Otherwise
the ADDRESS function does not get updated when opening the workbook on a
system with a different language. I could have used Application.CalculateFull
in the Workbook_Open event, but this solution does not need VBA.

So, mR & "[-1]" & mC can be used instead of "R[-1]C" and it's
language-independent.

Thanks for the assistance.
-=Wim=-

I don't use multiple languages, but if I wanted to check to see if A1 is locked,
I could use this in English:

=CELL("protect",A1)

I'm gonna guess that "protect" would change with different language
versions--protekt????

It may be worth a look to verify.



Wim SKW wrote:

Hi Harlan,

"Harlan Grove" wrote:

....
I have a named range "IsLocked" which refers to
=GET.CELL(14,INDIRECT("RC",FALSE)). I conditionally format cells with
=IsLocked to automatically set the background color of the cell to grey.
The unlocked cells remain white so the user has a visual clue as to
which cells he can modify.
....

Finally a valid use for INDIRECT(ADDRESS(...)) - internationalization.

=GET.CELL(14,INDIRECT(ADDRESS(0,0,4,0),0))

Brilliant!
The ADDRESS function did the trick.
I now defined two names: mR which refers to =LEFT(ADDRESS(0,0,4,NOW()*0),1)
and mC =RIGHT(ADDRESS(0,0,4,NOW()*0),1).
NOW()*0 always evaluates to 0 but it forces Excel to recalculate. Otherwise
the ADDRESS function does not get updated when opening the workbook on a
system with a different language. I could have used Application.CalculateFull
in the Workbook_Open event, but this solution does not need VBA.

So, mR & "[-1]" & mC can be used instead of "R[-1]C" and it's
language-independent.

Thanks for the assistance.
-=Wim=-

--

Dave Peterson

Hi Wim,

The easiest way is to use the A1 reference style.
But if you have a cell with a simple formula, like =A2 in A1, you can use this VBA function to find out what it looks like in the
local language:

Function RefStyle()
RefStyle = [a1].FormulaR1C1Local
End Function

BTW, All text literals can cause problems in other language versions: format codes, arguments for DATEDIF functions, for EVALUATE,
for DATEVALUE, etc.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Wim SKW" wrote in message ...
| Hi,
| In a cell I used a formula like =INDIRECT("R[-1]C",FALSE).
| This works fine on an English version of Excel, but if you open the workbook
| on a system where the language is set to French for instance, the formula
| returns a #REF! error.
| In French, the formula should be =INDIRECT("L[-1]C",FALSE), (i.e. L for
| Ligne and C for Colonne).
| On a Dutch system, Excel translates the *function* to
| =INDIREKT("R[-1]C",FALSE) but the #REF! error remains because it should be
| =INDIREKT("R[-1]K",FALSE) (i.e. R for Rij and K for Kolom).
| On a Germans system or a Spanish system or ...(I can go on like that...),
| the problem is similar.
|
| Can anyone provide me with a reliable way to determine how the words "Row"
| and "Column" are abbreviated on the current system where Excel is running so
| I can use these characters in the INDIRECT function?
|
| I searched this newsgroup and I found a zillion messages for the R1C1
| reference style problem, but no one seems to have encountered this problem
| (or maybe they found the solution themselves :-)
|
| Any help appreciated.
| -=Wim=-