Needed help-German Excel 2010 Formulas vs. English Excel 2007

[excelianer]

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

[rollis13]

The problem probably is not German/English but 2010/2007. Formulas have to be rewritten. For the translation try to help yourself with: http://excel-translator.com/

[excelianer]

...thank you very much for your response. I translated it through your added link
http://excel-translator.com/
Well I can’t taste it now in office English version. Next two days I am off so I can try that on Thursday.
Only different which I see there is colon and semicolon.
German-“=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))”

Versa English-“=IF(ISNA(VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&1;'1R'!$A$2:$C$2000;3;0));"";VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&1;'1R'!$A$2:$C$2000;3;0))&IF(ISNA(VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&2;'1R'!$A$2:$C$2000;3;0));"";CHAR(10)&VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&2;'1R'!$A$2:$C$2000;3;0))&IF(ISNA(VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&3;'1R'!$A$2:$C$2000;3;0));"";CHAR(10)&VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&3;'1R'!$A$2:$C$2000;3;0))&IF(ISNA(VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&4;'1R'!$A$2:$C$2000;3;0));"";CHAR(10)&VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&4;'1R'!$A$2:$C$2000;3;0))&IF(ISNA(VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&5;'1R'!$A$2:$C$2000;3;0));"";CHAR(10)&VLOOKUP(TEXT(F7;"TT.MM.JJJJ")&"-"&5;'1R'!$A$2:$C$2000;3;0))”

And '=TEXT(G29,"TT.MM.JJJJ")&"-"&COUNTIF($B$2:G29,G29) vs. '=TEXT(G29,"TT.MM.JJJJ")&"-"&COUNTIF($B$2:G29,G29)
I am not sure if this is going to work in English office. What about formatting “TT.MM.JJJJ?
Could someone please try this formula in English office for me????

Thanks in advance

Excelianer

[ConneXionLost]

How about:

Please Login or Register  to view this content.

[excelianer]

Hi Conne,

Thank you for response.
Did you try that in English office?
I noted it that I can try this formula on Thursday in company where I am using English office than I will see does it have to do only with formatting “DD.MM.YYYY”.

Thanks

Excelianer

[ConneXionLost]

Yes, I didn't have any data, but Excel (english) accepted the formula as written.

[rollis13]

[...] through your added link http://excel-translator.com/ [...] Only different which I see there is colon and semicolon.[...]

During translation you need to indicate the needed separator in the Option section.

[excelianer]

Sorry in previous posting excel file was not nice sorted.
Here we go……



Intern11223344.xlsm

[excelianer]

Thanky you very much for your resnose's and advice's.
Yes that is true that excel accept formula as written. That’s why I like to ask you to try that formula in English excel. Like I mentioned it in first posting as I opened excel map “Intern11223344.xlsm” in company with English excel whole formula were translated:
=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))
Only what I am not sure that could be German format "TT.MM.JJJJ" and this format I am going to change it in company on Thursday. But It could be maybe anything else wrong in formula or formats.
My excel knowledge is not very well. Those formulas I got from German Excel forum and they advised me to post my concern to one English Excel forum. If somebody can open

Intern11223344.xlsm

And have look in following sheets “2013 Landscape”, “1R”, “2E” and “3F”.
In sheet “2013 Landscape” is formula in row 4th “=IF(ISNA(VLOOKUP(TEXT(I29,"TT.MM.JJJJ")&"-"&1,$A$2:$C$2000,3,0)),"",VLOOKUP………..” and sheets “1R”, “2E” and “3F” are the source sheets where the dates are. And those dates I like to have in “2013 Landscape” sheets. If someone could check those formulas and format’s on English Excel that would be awesome.

Thanks for coming response

Excelianer

[rollis13]

You could use a macro with .FormulaR1C1 = "= ... " to insert two different formulas in the cell so translation is automatic. The only difference in the formulas will be the formatting ("TT.MM.JJJJ" / "DD.MM.YYYY"). The macro needs to be run only once.

[excelianer]

Hallo Rollis13,

That would be maybe the best solution.
I am so sorry that my VBA knowledge is zero (only Marco recorder).

Could you please help me with some example to have a macro for both versions???
So I could use at home German formula and in company I could very easy switch to English formula.
And how would it work for whole “2013 Landscape” sheet? I have a landscape calendar there (from January till December) “C6:K2989”. But the good thing is that between whole calendar weeks I have 5 free columns from “E:K”.
Is it possible that instead of formulas Macro put whole comments on in day’s rows “E6:K6?
I put some more cements again in excel map:

example map.xlsm

Yellow background color in columns “E:K” are for cements provided.
If macro could do whole formulas included function “takecoment” for whole formulas that are in yellow background color that would be really great.

Thanks for coming help and response

[excelianer]

Still not sure if my (prev.) description were understandable.
Can anyone help me to see if all formula are right and have right format which is working in English excel version???? I changed German format from “TT.MM.JJJJ” to “DD.MM.YYYY”
- “2013 Landscape” formulas to check have yellow background color
- “1R” formulas to check have green background color
- “2E” formulas to check have yellow background color
- “3F” formulas to check have blue background color


looking for engl. formula.xlsm



My explanation to formulas for target sheet “2013 Landscape”:
I like to have all appointments or deadlines from sheets (1r, 2e and 3f) in sheet “2013 Landscape” in columns “E4:K4”. And from those columns “E4:K4” I have all deadline’s in comments (with function help “takecomments”) in day’s cells “E6:K6”. For example in cell “F6” is Tue=for Tuesday (has custom format “DDD”)). This is working well in German office. But on the work in company with English office is not working.
I’ve chosen formula cell “F4” ‘=IF(ISNA(VLOOKUP(TEXT(I29,"DD.MM.YYYY")&"-"&1,$A$2etc….” because this formula can show more than one appointments per single days.


My explanation to formulas for source sheet “1r, 2e and 3d”:
- column “A” count how many appointments are in one days
- column “D” is help to get sorted date’s
- column “E” has formula which I like to use in target sheet “2013 Landscape”

If those formula are wrong or complicate or if you have better ideas please let me know.
What do you think about this formula? Could be function “takecomment” offset and mach… better than previous example???!!!
German formula:
‘=TakeComment($C4;BEREICH.VERSCHIEBEN(sheet2!$B$4;VERGLEICH($A4;sheet2!$A$5:$A$25;0);VERGLEICH($B4;sheet2!$C$4:$Z$4;0);1;1))
English formula:
‚=TAKECOMMENT($C4,OFFSET(SHEET2!$B$4,MATCH($A4,SHEET2!$A$5:$A$25,0),MATCH($B4,SHEET2!$C$4:$Z$4,0),1,1))




The sample file is attached.

Any help is greatly appreciated!

[excelianer]

Hi everybody,

I was wondering if you could help me please with a formula and format eg. German/English office.
Could someone lookt into this file attached file "looking for engl. formula.xlsm‎"
and let me know are this formula/format working in Engl. office?????

I am very grateful for comming help.
Thanks again.
Excelianer