Excel - Indirect function with national language

Hi,

I read in a post on this site (by Niek Otten in July 2009)that we can have
problem with the Indirect function in Excel when we work with people with
other languages.

My collegue work with Excel 2003 in English and when he use the Indirect
function, he do not have any error.  If I open the same file on the network in
Excel 2003 in French, I see #Ref! error?

Can I modify something to do not see the error?

I have already change my regional setting to English and I do not have any
more idea

I am using Windows XP SP3 in French with Excel 2003 SP3 in French.  My
collegue is working with Windows XP SP3 in English with Excel 2003 SP3 in
English.

Thanks,
Catherine

You should post the Indirect formula!
--
Regards!
Stefi



???catherine??? ezt ??rta:

Hi Stefi

This is the formula

=SUM(INDIRECT(+("R5C"&COLUMN(INDEX(AA9:CB9,MATCH(9.99999999999999E+307,AA9:CB9)))&":R5C")&COLUMN(INDEX(AA9:CB9,MATCH(9.99999999999999E+307,AA9:CB9))),FALSE))

When in Evaluate the formula (before the error in French) it is giving me
SUM(INDIRECT(R5C45:R5C45",FALSE))

If you need more information, let me know

Thanks for you help

Hi Stefi,

The formula give return a date.  The format of the date is [$-1009]d/mmm;@.
If I change it to General category, I have the error too in French.  If I
change 1009 for 0c0c (French Canada), I have the error in French.  If I
change my regional options to English Canada, I have the error.

I tried to do a find and replace of the Indirect function (solution propose
by Microsoft to solve the ATP function error), and I have the error message
in French.

Thanks
Catherine

The formula uses R1C1 reference style. Unfortunately these letters (R for
row, C for column)  have been translated into their national language
equivalents, check it Tools>Options>General tab, check box caption in top
left corner. Replace R's and C's in "R5C" parts of the formula by their
national language equivalents, or rather both of you should use the formula
below, it gives the same result but independent on  national languages:

=INDIRECT(ADDRESS(5,COLUMN(INDEX(AA9:CB9,MATCH(9.99999999999999E+307,AA9:CB9))),4,1))

--
Regards!
Stefi



???Catherine??? ezt ??rta:

You may want to read Ron de Bruin's site:
http://www.rondebruin.nl/international.htm


--

Dave Peterson

Hi Stefi,

You are right, I did not think to that. I will suggest you new formula to
do not have to change the original one at each time

Many thanks

You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated.

--
Regards!
Stefi



???catherine??? ezt ??rta: