Hi Excel Frieds,

I am going to write in English Forum in hope to get some help for office English version.

I have 4 sheets, one sheets is “2013 Landscape” and another 3 have diverse dates (1R, 2E, 3F).

I find out in German excel forums help how to get more than 3 dates per day in “2013 Landscape” sheet. Everything works perfect in German office 2010 but in English office2007 in company where I am working I get error of “#VALUE!”.

At home I prepared on Excel 2010 (German Version) formulas which are not working in office 2007 (English version).

German Formulas:

‘=WENN(ISTNV(SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&1;'1R'!$A$2:$C$2000;3;0));"";SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&1;'1R'!$A$2:$C$2000;3;0))&WENN(ISTNV(SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&2;'1R'!$A$2:$C$2000;3;0));"";ZEICHEN(10)&SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&2;'1R'!$A$2:$C$2000;3;0))&WENN(ISTNV(SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&3;'1R'!$A$2:$C$2000;3;0));"";ZEICHEN(10)&SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&3;'1R'!$A$2:$C$2000;3;0))&WENN(ISTNV(SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&4;'1R'!$A$2:$C$2000;3;0));"";ZEICHEN(10)&SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&4;'1R'!$A$2:$C$2000;3;0))&WENN(ISTNV(SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&5;'1R'!$A$2:$C$2000;3;0));"";ZEICHEN(10)&SVERWEIS(TEXT(F7;"TT.MM.JJJJ")&"-"&5;'1R'!$A$2:$C$2000;3;0))

English formulas with error massage “#VALUE!”:

'=IF(ISNA(VLOOKUP(TEXT(E7,"TT.MM.JJJJ")&"-"&1,'1R'!$A$2:$C$2000,3,0)),"",VLOOKUP(TEXT(E7,"TT.MM.JJJJ")&"-"&1,'1R'!$A$2:$C$2000,3,0))&IF(ISNA(VLOOKUP(TEXT(E7,……

And

German Formulas:

'=TEXT(G29,"TT.MM.JJJJ")&"-"&COUNTIF($B$2:G29,G29)

English formulas with error massage “#VALUE!”:

=IF(ISNA(VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&1,$A$2:$C$2000,3,0)),"",VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&1,$A$2:$C$2000,3,0))&IF(ISNA(VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&2,$A$2:$C$2000,3,0)),"",CHAR(10)&VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&2,$A$2:$C$2000,3,0))&IF(ISNA(VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&3,$A$2:$C$2000,3,0)),"",CHAR(10)&VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&3,$A$2:$C$2000,3,0))&IF(ISNA(VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&4,$A$2:$C$2000,3,0)),"",CHAR(10)&VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&4,$A$2:$C$2000,3,0))&IF(ISNA(VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&5,$A$2:$C$2000,3,0)),"",CHAR(10)&VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&5,$A$2:$C$2000,3,0))

And with Macro helps "TakeComment" I get comentsIntern11223344.xlsm result in rows where I have day names.

I added example map. Could you please have a look what is wrong in formulas?

I guess “TT.MM.JJ=>DD(days).MM(mounths).YYYY(years)” or is anything else what is needed to be done to have exactly results in English office 2007?!

Thank you very much for coming help and comments.

Excelianer

## Bookmarks