Forum Discussion

jalverson's avatar
jalverson
Copper Contributor
May 19, 2020

Complex autofill formula

I am creating a column of cells (column e) on a data sheet that I want to auto-fill with the contents of a different cell from  datasheet named Blad 1 (column A) if the contents in column F fall within a range of numbers within the Åtgärderskoder datasheet's column B and return nothing if it does not match that criteria.  I have completed the initial cell formula which seems to work and returned the result I expected, but when I drag down to auto format, the autofill places a combination of different results (several cells from the Blad 1 datasheet into the same cell).  For example: (is in Swedish (om=if, och=and, eller=or, ;=,) I apologize for the Swedish formula, but I do not write swedish well and needed help on an english forum.)....

 

=OM(ELLER(F2>=Åtgärdskoder!$B$4;F2<=Åtgärdskoder!$B$25;F2>=Åtgärdskoder!$B$30;F2<=Åtgärdskoder!$B$46);Blad1!$A$3;"")&OM(OCH(F2>=Åtgärdskoder!$B$26;F2<=Åtgärdskoder!$B$29);Blad1!$A$4;"")&OM(OCH(F2>=Åtgärdskoder!$C$47;F2<=Åtgärdskoder!$B$216);Blad1!$A$7;"")&OM(OCH(F2>=Åtgärdskoder!$B$217;F2<=Åtgärdskoder!$B$255);Blad1!$A$8;"")&OM(OCH(Plan!F2>=Åtgärdskoder!$B$256;Plan!F2<=Åtgärdskoder!$B$277);Blad1!$A$8;"")&OM(OCH(F2>=Åtgärdskoder!$B$278;F2<=Åtgärdskoder!$B$332);Blad1!$A$6;"")&OM(OCH(F2>=Åtgärdskoder!$B$333;F2<=Åtgärdskoder!$B$337);Blad1!$A$15;"")&OM(OCH(F2=Åtgärdskoder!$B$338);Blad1!$A$9;"")&OM(OCH(F2>=Åtgärdskoder!$B$339;F2<=Åtgärdskoder!$B$350);Blad1!$A$11;"")&OM(OCH(F2>=Åtgärdskoder!$B$351;F2<=Åtgärdskoder!$B$355);Blad1!$A$13;"")&OM(OCH(F2>=Åtgärdskoder!$B$356;F2<=Åtgärdskoder!$B$378);Blad1!$A$10;"")  

 

Result returns as Code 2 (which is what I expected)

However, when I drag down to autoformat the formula, the result vary and will return Code 2 and code 7 or some other number that it matches.  How do I rectify this or is there a better formula to utilize multiple contingencies?

 

Any help would be appreciated if it is possible.

 

Thanks!

 

I have loaded a copy of the workbook and the sheet I am configuring is the one named Plan.  I have set notes in the cells in Column E so you understand better what is resulting.

 

 

excel

6 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 21, 2020

    jalverson Can't help thinking that you are choosing a complicated manner to achieve something that's fairly easy if you create an intermediate table where you link "Åtgärdskoder" with "Komponentkoder". The key seems to lie in the first two digits of the Åtgärdskod. I created such a table in "Blad 1" and referred to it in column E (Plan) with a simple VLOOKUP. You need to check if I mapped the correct Komponent to the correct Åtgärd groups.

     

    Please have a look at the attached file.

     

    Trevlig helg!!

    • jalverson's avatar
      jalverson
      Copper Contributor
      May 21, 2020

      Riny_van_Eekelen This worked great.  It is exactly what I was trying to achieve in a complex manner.  I haven't used VLOOKUP in about 15 years and forgot about it.  Thanks for the help!

  • Austris Bahanovskis's avatar
    Austris Bahanovskis
    Brass Contributor
    May 20, 2020
    jalverson 

    It's a bit tricky to read your formula...

    If you'd share a sample file - it would be easier to grasp and provide a solution.

    My gut feel is use sumproduct + choose

    The TRICK is that if you do a compare two values and change that into a number (proceed with double minus "--") - then if matches, that yields =1 but if it doesn't match, it yields =0

    For instance:

    =--("a"="a") << returns =1

    =--("a"="b") << returns =0

    So you can build your criteria and total the evaluations up - that yields integer that you can feed into the choose function.

    But as I said - if you'd upload a sample file - that would help (and potentially render my suggestion above totally useless :))
    • jalverson's avatar
      jalverson
      Copper Contributor
      May 20, 2020

      Austris Bahanovskis Thanks,

       

      I have uploaded a copy of the Workbook that is in question.  Again, in swedish, so I am not sure if you can read or open it.  However, I have placed notes with Yellow highlighting on the column in question.

      • Austris Bahanovskis's avatar
        Austris Bahanovskis
        Brass Contributor
        May 20, 2020

        jalverson 

        Hey!

        Sorry, got tied up all day...

        BTW: if I open office file in my local MsOffice - the formulas get translated into my locale. So, I don't need to know Swedish 😉 (I wouldn't mind, though!)

        Anyways, it's not that I've got a ready solution for you but this might help you (as I don't know if I'll have enough time to solve it entirely for you) - have a look at the 1st IF of your formula:

        this bit:

        F2>=Åtgärdskoder!$B$4

        with your data will ALWAYS return TRUE because the MIN(F) =11121, i.e., the smallest number in column [Åtgärds-kod] is =11121. Then in tab [Åtgärdskoder] column B also the smallest number is 11121, hence this comparison F2>=Åtgärdskoder!$B$4 (for the entire column F) will always return TRUE because any number in column F is greater than or equal to the Åtgärdskoder!$B$4.

        Now, since your first IF checks for OR, that makes it ALWAYS return TRUE which in turn ALWAYS makes the IF to return value "2" from Blad1!$A$3.

         

        Sure, there are various ways how to write this formula in a more elegant way but if the above solves your issue - maybe that's enough 🙂

        Post back if this doesn't resolve your issue < I'll try to check in again (cannot promise though...)

Resources