Complex autofill formula

[jalverson]

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.

[#2]

@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 :))

[#3]

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

[#4]

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

[#5]

@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!!

[#6]

@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!

[#7]

@Austris Bahanovskis Thanks for the answer.  I wasn't considering the if(or(>= would always return the the cell I was referring to in every cell.  It makes sense now.  Appreciate the input.  Thanks!