Indirect function with national language

[catherine]

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 don't 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 don't see the error?

I have already change my regional setting to English and I don't 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

 

[Stefi]

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



„catherine†ezt írta:

 

[Catherine]

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's giving me
SUM(INDIRECT(R5C45:R5C45",FALSE))

If you need more information, let me know

Thanks for you help

 

[Catherine]

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

 

[Stefi]

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:

 

[Dave Peterson]

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

 

[catherine]

Hi Stefi,

You are right, I didn't think to that. I will suggest you new formula to
don't have to change the original one at each time

Many thanks

 

[Stefi]

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

--
Regards!
Stefi



„catherine†ezt írta: