Formula Does not work on excel 2003

[Bernard Liengme]

Your formula works just fine for me in Excel 2003
When it is not present it returns a blank as expected.
If you want 0 then use
=IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")),0,INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12"))

A minor point: You state: "is supposed to do is pull data from a sheet
name in the format
dd.mm.yy" But the formula uses format dd-mm-yy with dashes not dots

best wishes

 

[Microsoft Communities]

This formula does not work in excel 2003. Does anyone know how to convert it
to the correct formula for excel 2003.
It works perfectly on my system with 2007. But when I put it on a system
with 2003 it returns nothing.

What it is supposed to do is pull data from a sheet name in the format
dd.mm.yy. Sometimes the sheet is not there so should return nothing or 0.


=IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")),"",INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12"))

 

[Microsoft Communities]

Yes I am sorry they are dashes. I do not know why it does not work then.
I am putting it on s system that is in Portuguese (Brazil) but I even
changed the date language to reflect that.

 

[Bernard Liengme]

Would you like to send me a sample file?
Get my email from my website

 

[Niek Otten]

I'm afraid changing the date language is not enough; you'll probably have to
change the format string (dd-mm-yy) to a Portugese string.

 

[Bernard Liengme]

Well done, Niek
We Anglophones are so English-centric!!
best wishes

 

[Ron de Bruin]

Portuguese (Brazil)

Maybe this is your problem ????

There is a problem with the TEXT function. The format string might not translate correctly if the workbook is opened in a different
language version of Excel. For example, consider the following.

="Today is " & TEXT(TODAY(),"yyyy-mm-dd")

In the English language version of Excel, the formula returns the following:

Today is 2005-02-23

However, the Dutch-language version year format uses jaar "jjjj-mm-dd" so the formula returns the following:

Today is yyyy-02-23

 

[Niek Otten]

I'm surprised the code for year is the same in Portugese: "y". In fact I
find it a bit hard to believe.
I really think you need a localized format string.
You can easily test it with a formula like

=TEXT(TODAY(),"dd-mm-yyyy")

What result does that give you?

 

[Microsoft Communities]

The date format for Brazil is dd-mm-yy and that is the format I am using.

 

[Microsoft Communities]

Oh I did not realize. In Brazil the yyy should be aaa I will check it out
thank you.

 

[Ron Rosenfeld]

Yes I am sorry they are dashes. I do not know why it does not work then.
I am putting it on s system that is in Portuguese (Brazil) but I even
changed the date language to reflect that.

TEXT(A1;"dd-mm-aa")

You're using the wrong date format string for Brazilian Portuguese.
--ron