I have a formula I've successfully used in a spreadsheet for over seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable version
of the syntax to work like it has here in the States for so many years.

Thanks,
Chuck

Chuck,

Are you sure they are entering exactly the same formula and not

=COUNTIF(A1:A18;D2)1

Normally, Belgium would use semi-colon as an argument identifier/delimiter,
whereas you would use comma. Building that up maintains the syntax.

Good summary BTW, very clear.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"c mateland" wrote in message
ups.com...
I have a formula I've successfully used in a spreadsheet for over seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable version
of the syntax to work like it has here in the States for so many years.

Thanks,
Chuck

Some countries use a semicolon rather than a comma in formulae - could
it be something like that? Have you tried setting the Local Settings of
the PC in Belgium to act as if it were in USA, load the Excel file in
and then change the Regional Settings back to Belgium? If this works,
then you have your Excel file translated for the Belgium office.

Hope this helps.

Pete

c mateland wrote:

I have a formula I've successfully used in a spreadsheet for over seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable version
of the syntax to work like it has here in the States for so many years.

Thanks,
Chuck

Thanks, Bob.

Actually, I sent the formula in text so they would copy and paste the
formula in, preventing inconsistancies in the syntax.

But, I'd still like to investigate the comma/semi-colon issue more with
them. Is there more I should know about that? Is that true in all of
Europe? Who uses commas and who uses other delimiters for arguments?
Any other USA/Europe snaffu's to look for? This is new to me. Thanks
for the help.

Anyway, if that turns out the be the problem, I'll have to create a
Belgium version of the workbook, I guess? That's not going to be fun,
because I use hundreds of formulas, many of them entered and maintained
via VBA, userforms, and thousands of lines of code. <g

I'll let you know what I find out.

Thanks,
Chuck


Bob Phillips wrote:
Chuck,

Are you sure they are entering exactly the same formula and not

=COUNTIF(A1:A18;D2)1

Normally, Belgium would use semi-colon as an argument identifier/delimiter,
whereas you would use comma. Building that up maintains the syntax.

Good summary BTW, very clear.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"c mateland" wrote in message
ups.com...
I have a formula I've successfully used in a spreadsheet for over seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable version
of the syntax to work like it has here in the States for so many years.

Thanks,
Chuck

Excellent idea, to check the regional settings, Pete. They told me they
were US, but I didn't actually verify that.

So, if set to Dutch (Belgium), that would change Excel's argument
delimiter from a comma to a semi-colon?

Thanks,
Chuck

Pete_UK wrote:
Some countries use a semicolon rather than a comma in formulae - could
it be something like that? Have you tried setting the Local Settings of
the PC in Belgium to act as if it were in USA, load the Excel file in
and then change the Regional Settings back to Belgium? If this works,
then you have your Excel file translated for the Belgium office.

Hope this helps.

Pete

c mateland wrote:

I have a formula I've successfully used in a spreadsheet for over seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable version
of the syntax to work like it has here in the States for so many years.

Thanks,
Chuck

I don't know, as I have no experience of this, but it's something quick
you could try.

Pete

c mateland wrote:

Excellent idea, to check the regional settings, Pete. They told me they
were US, but I didn't actually verify that.

So, if set to Dutch (Belgium), that would change Excel's argument
delimiter from a comma to a semi-colon?

Thanks,
Chuck

Pete_UK wrote:
Some countries use a semicolon rather than a comma in formulae - could
it be something like that? Have you tried setting the Local Settings of
the PC in Belgium to act as if it were in USA, load the Excel file in
and then change the Regional Settings back to Belgium? If this works,
then you have your Excel file translated for the Belgium office.

Hope this helps.

Pete

c mateland wrote:

I have a formula I've successfully used in a spreadsheet for over seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable version
of the syntax to work like it has here in the States for so many years.

Thanks,
Chuck

The other big snafu is dates in VBA. VBA is very US centric on dates,
whereas we use dd/mm/yyy format over here.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"c mateland" wrote in message
ups.com...
Thanks, Bob.

Actually, I sent the formula in text so they would copy and paste the
formula in, preventing inconsistancies in the syntax.

But, I'd still like to investigate the comma/semi-colon issue more with
them. Is there more I should know about that? Is that true in all of
Europe? Who uses commas and who uses other delimiters for arguments?
Any other USA/Europe snaffu's to look for? This is new to me. Thanks
for the help.

Anyway, if that turns out the be the problem, I'll have to create a
Belgium version of the workbook, I guess? That's not going to be fun,
because I use hundreds of formulas, many of them entered and maintained
via VBA, userforms, and thousands of lines of code. <g

I'll let you know what I find out.

Thanks,
Chuck


Bob Phillips wrote:
Chuck,

Are you sure they are entering exactly the same formula and not

=COUNTIF(A1:A18;D2)1

Normally, Belgium would use semi-colon as an argument
identifier/delimiter,
whereas you would use comma. Building that up maintains the syntax.

Good summary BTW, very clear.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"c mateland" wrote in message
ups.com...
I have a formula I've successfully used in a spreadsheet for over
seven
years in the USA (spanning multiple users and Excel versions). A group
in Belgium is now trying to use it, and getting an "Error in formula"
error. They have the exact same computer set up we use (Excel 2003),
yet the formula doesn't work over there. They have tried on several
machines.

Anyone know why this might be? Or any debugging strategies? Any
insight
or guesses at all?

Details...

Where SerArea is named dynamic named range in column A.
Where LetIdNum is a named cell (value is "D")

The variable syntax is this:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

That gets the error when entering it ONLY in Belgium.

Without variables, it would evaluate to this simple syntax:

=COUNTIF(A1:A18,D2)1

Funny thing, when entered in that simple format in Belgium, it WORKS.

Then, in Belgium, we replace a constant for a variable and it still
works:

=COUNTIF(A1:A18,INDIRECT(LetIdNum&ROW()))1

Then, in Belgium, we replace the last constant to a variable and it
still works:

=COUNTIF(SerArea,INDIRECT(LetIdNum&ROW()))1

Guess what? NOW, it's been edited back to the original syntax it
refused to accept before. It threw an error when we put it in directly
like that, but when we put it in as simple syntax and then edit it to
this it works. Is that crazy or what?

Any helpful comments would be great because I need the variable
version
of the syntax to work like it has here in the States for so many
years.

Thanks,
Chuck