Bilingual workbook problem

[A. Toczko]

I have programmed a worksheet in English, which is being used by someone who
has set his keyboard to French on his pc. The problem here is that I have
formulas which use month names, and on his pc the months show in French, so
the formulas no longer work. When he sends the sheet to me, it shows in
English, and all is well. How do I get around this? Thanks.

 

[Peo Sjoblom]

A workaround would be to use month names derived from formulas as opposed to
hard coded month names. Can you five an example of a formula and the month
it is referring to

 

[A. Toczko]

This is the formula I am using:

=SUMPRODUCT(--(Data!$S$2:$S$1415=A6),--(Data!$T$2:$T$1415="C"))
where A6 contains the month name April.

When on the French pc, column S contains various month names, which appear
in French, so when it compares it against A6, which is in English, there is
never a match. On the English pc, all works well of course.

Column S takes the month from another column, showing the month as format
MMMM. Is there a way to lock MMMM into showing English? That would solve
the problem.

Alison

 

[Arvi Laanemets]

Hi

Replace the entry in A6 with a date like 01.04.2005 formatted as custom
"mmmm"
The formula will now be
=SUMPRODUCT(--(Data!$S$2:$S$1415=TEXT(A6,"mmmm")),--(Data!$T$2:$T$1415="C"))


Arvi Laanemets

 

[Aladin Akyurek]

I'd suggest entering 4/1/05 (1-Apr-05) in A6, that is, the first day
date of the month/year of interest and invoke:

=SUMPRODUCT(--(DATE(YEAR(Data!$S$2:$S$1415),MONTH(Data!$S$2:$S$1415),1)=A6),--(Data!$T$2:$T$1415="C"))

which would be immediately readable in French.

 

[Aladin Akyurek]

Hi

Replace the entry in A6 with a date like 01.04.2005 formatted as custom
"mmmm"
The formula will now be
=SUMPRODUCT(--(Data!$S$2:$S$1415=TEXT(A6,"mmmm")),--(Data!$T$2:$T$1415="C"))

[...]

This would work because "month" and "mois" has the same first letter. It
wouldn't if it was "yyyy", for example.

 

[A. Toczko]

Thank you for your help.

Alison

I'd suggest entering 4/1/05 (1-Apr-05) in A6, that is, the first day date
of the month/year of interest and invoke:

=SUMPRODUCT(--(DATE(YEAR(Data!$S$2:$S$1415),MONTH(Data!$S$2:$S$1415),1)=A6),--(Data!$T$2:$T$1415="C"))

which would be immediately readable in French.