Forum Discussion
Complex autofill formula
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 :))
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.
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...)
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!