+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    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

  2. #2
    Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    332

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

    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/

  3. #3
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    ...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

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

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

    How about:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  5. #5
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    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

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

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

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

  7. #7
    Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    332

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

    Quote Originally Posted by excelianer View Post
    [...] 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.

  8. #8
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

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

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



    Intern11223344.xlsm

  9. #9
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

    Lightbulb Re: Needed help-German Excel 2010 Formulas vs. English Excel 2007

    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

  10. #10
    Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    332

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

    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.

  11. #11
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

    Lightbulb Re: Needed help-German Excel 2010 Formulas vs. English Excel 2007

    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

  12. #12
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

    Exclamation Re: Needed help-German Excel 2010 Formulas vs. English Excel 2007

    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!
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-06-2011
    Location
    Rosenheim, Germany
    MS-Off Ver
    Excel 2007
    Posts
    19

    Exclamation Re: Needed help-German Excel 2010 Formulas vs. English Excel 2007

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1