VBA or Formula for 35 nested IF AND statements

[SierraDelta]

Hi All,

I've been struggling with this problem all day. I've searched through existing posts for nesting statements, but my scenario doesn't match with anything I've found so far.

I have 3x different drop down lists. The combination of their values form the qualifiers for the numeric result at the end of my current IF AND statement. I have the "Total Hours" values for each combination - I don't mind entering them manually.

The following statement works, but obviously only for 1 combination. I need this logic for 36 x3 combinations. If the script or formula can allow me to keep entering the Total Hours values manually that would be perfect.

=IF(AND(D6="Care Report";G6="Chaos";K6="1");"26";"26")

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Assessment Type
(Drop Down)[/TD]
[TD]Maturity Level
(Drop Down)[/TD]
[TD]Data Quantity
(Drop Down)[/TD]
[TD]Total Hours
(Fixed formula Answer Cell)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Report 1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD](see formula above)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Report 2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Report 3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




































I would be very grateful for any guidance/help.

Thanks,
SD

 

[Eric W]

Functionally, the concept behind Dr. Demento's VBA function (or stumac's) and my worksheet formula is the same. In each case a table is created of the combinations, and the routine searches the table for a match. His table is in the macro, mine is in another location in the workbook. The main difference is whether you want to use VBA or not. Some people don't want to use VBA because they don't understand it, or workplace restrictions forbid it.

If you're happy with a VBA solution, by all means go that direction. Let me know if you have questions about a formula-based solution.

 

[Dr. Demento]

Dr. Demento, would you still be able to put an example worksheet together for me?

stumac has the correct approach (I would have gotten there eventually). My initial approach of using a Worksheet_Change has a huge amount of overhead (it runs everytime there's a change/data entry/etc on the sheet). That would make for a very slow process. stu's approach is nice because it allows you to enter the values independently (which is good because the setup you provided is nice for data presentation but poor for data entry).

However, as Eric pointed out, each of our methods are fundamentally the same; only the approach varies.

Is stumac's example enough to get you going??

 

[SierraDelta]

I believe it is. I'm new to VBA but will Google and sticky tape it. I've just been given another project to complete but will be able to use this over the weekend. I will then indicate whether or not I could get it going. Thanks!