Using IF/VLOOKUP formulas INSIDE Data Validation

**NBVC** · 09-07-2011, 03:05 PM

I think you just lost me... E9, M9? Fast_Attack FOS, Thunder Hammers?

Please use the Varro Tigurius option when explaining and give exact details of what is supposed to happen, where and why?

**MavGunloc** · 09-07-2011, 04:48 PM

Oh I'm sorry, didn't mean to confuse you. Sadly, I can't use Varro Tigurius for this example.

Under the Options/Upgrades there are two columns. If you select Fast_Attack under the Force Organization slot then Vanguard_Veteran_Squad under the unit squad, almost every cell in both columns will be filled. In order to properly total all of the points for the upgrades, I would need to add both adjacent columns together (ones in the same row)

So in the first cel of the first column, you can select Additional Units X1-5 and a point total will populate in the points column. In the first cel of the second column, you can select Thunder Hammer X1-10 and each of those selections has a certain number of points its worth. To keep things simple, I would just like each row to add together the totals and put it in the points column. For example, in the Vanguard_Veteran_Squad, if you select Additional Units X5 and Thunder Hammer X10, the total points for that row should equal 400.

**NBVC** · 09-07-2011, 05:06 PM

I think I understand now

Try:

=IF(AND(E9="",M9=""),"",SUMIF(OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,1),E9,OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,2))+SUMIF(OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,1),M9,OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,2)))

**MavGunloc** · 09-07-2011, 05:51 PM

It works! +1 to science.

Thank you very much for all of your help. I truly appreciate you spending the time to help me out with my problem.

**MavGunloc** · 09-08-2011, 12:39 PM

You know what, I found something kind of wonky with the point totals for the option upgrades.

If you select HQ in the Force Organization Slot and then pick Space_Marine_Chapter_Master or Space_Marine_Captain, the point cost for the first 2 upgrade slots are doubled for some odd reason. It doesn't happen with any other unit (that (I've seen thus far).

For example, when you select Relic Blade in the first option drop down list it should be 30 points, not 60.

I've attached the latest version of this document.

**NBVC** · 09-08-2011, 01:27 PM

It is because those 2 options have identical column details duplicated in their respective table in the OptionsV2 tab.

If the respective numbers are going to be the same for each category as it looks like in the OptionsV2 tab, then we can change the SUMIF to AVERAGEIF... so that it divides the sum by the count, in essence, giving you the correct expected end result.

put this formula in Q9:

=IF(AND(E9="",M9=""),"",IF(E9="",0,AVERAGEIF(OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,1),E9,OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,2)))+IF(M9="",0,AVERAGEIF(OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,1),M9,OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,2))))

copied down.

If the numbers are not the same, then it will get more complicated and you will need to fully separate the column E options and column M options within the OptionsV2 sheet into 2 separate groups for each Unit Name.