Thread: Using IF/VLOOKUP formulas INSIDE Data Validation

Hello there everyone.

I've been having a whopper of a problem and for the life of me, I can't figure it out. Hopefully someone out there in the Excel Forum universe can help alleviate my dilemma.

What I have attached is spreadsheet I've built to help quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.

For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.

What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.

Oddly enough, when I enter this formula under Data Validation:

=IF($A$3<>$AD$1:$AD$182,VLOOKUP(A3,Options,2,FALSE),"")

I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.

Any help or insight into this problem would be highly appreciated. If at all possible, I'd like to try to avoid using VBA but I wouldn't be opposed to using small macros (I've never worked with either before).

Thanks in advance!

Using a data validation formula like this:

=OFFSET(INDIRECT($A$3),0,1,1,COUNTA(INDIRECT($A$3)))

will get you all the items in that named range.... you won't be able to get just the text strings from those ranges though. You would need to create subsets in the sheet of each of those ranges, and reference those instead, if that is what you need.

The formula I gave is dependent on the option selected in A3. So if you select Rhino in A3, the options in the dropdown get updated to reference the Rhino named range.

Not sure what you mean otherwise? Can you give definite example situation and outcome expected?

Okay, I think I understand better....

But you will need to be more consistent with the formatting of your lookup tables in AD onward. For example for the Varro Tigrius table, move the second column over to the right by one, so that every other column has the text to return.

Then name each range consistently starting from the the name in Column AD to the bottom right cell of the last column in the table. Note for multi-word tables like Varro Tigrius, you cannot have spaces... so even though cell AD2 has "Varro Tigrius", you must name the range as "VarroTigrius".

Then for data validation formula for E9 use:

=OFFSET(INDIRECT(SUBSTITUTE($A$3," ","")),0,1,COUNTA(INDEX(INDIRECT(SUBSTITUTE($A$3," ","")),0,2)-1),1)

in E10, slightly change to:

=OFFSET(INDIRECT(SUBSTITUTE($A$3," ","")),0,3,COUNTA(INDEX(INDIRECT(SUBSTITUTE($A$3," ","")),0,4)-1),1)

The E11:

=OFFSET(INDIRECT(SUBSTITUTE($A$3," ","")),0,5,COUNTA(INDEX(INDIRECT(SUBSTITUTE($A$3," ","")),0,6)-1),1)

and so on with same pattern.

It worked for me using my method. See attached sample. I have Varro Tigrius in A3 and named the range Varro_Tigrius

If you use underscores, the the SUBSTITUTE functions have to account for that, e.g. SUBSTITUTE($A$3," ","_")

I only did the validation in E9 and E10

I see what you mean.

Try instead this simpler data validation formula in E9

=INDEX(INDIRECT(SUBSTITUTE($A$3," ","_")),0,2)

and in E10

=INDEX(INDIRECT(SUBSTITUTE($A$3," ","_")),0,4)

and so on

Success! It works! This is awesome. Thank you so much.

One minor thing, not that much of a big deal, but is it possible to take all of that data on the right side of the sheet and:

1) Move it to a new worksheet
or
2) Hide it out of view

Select all those columns and CUT (CTRL+X), then paste into a new sheet... the references should follow....

If I understand correctly, try:

In R9:

=IFERROR(VLOOKUP(E9,OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,1,,2),2,0),"")

in R10:

=IFERROR(VLOOKUP(E9,OFFSET(INDIRECT(SUBSTITUTE($A$3," ","_")),0,3,,2),2,0),"")

and so on.

You can enter the formula once in R9 and copy down, then just change the Bold Red number in each formula by adding 2 as you go down.