Excel - Indirect function with national language

Asked By catherine on 23-Feb-10 09:46 PM
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




Stefi replied to catherine on 24-Feb-10 03:58 AM
You should post the Indirect formula!
--
Regards!
Stefi



???catherine??? ezt ??rta:
Catherine replied to Stefi on 24-Feb-10 06:07 AM
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
Catherine replied to Stefi on 24-Feb-10 06:33 AM
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 replied to Catherine on 24-Feb-10 08:08 AM
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 replied to catherine on 24-Feb-10 09:05 AM
You may want to read Ron de Bruin's site:
http://www.rondebruin.nl/international.htm


--

Dave Peterson
catherine replied to Stefi on 24-Feb-10 09:08 AM
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
Stefi replied to catherine on 24-Feb-10 09:27 AM
You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated.

--
Regards!
Stefi



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