MS Excel advanced help

altmann

I'm making a custom army builder spreadsheet for space wolves. A perfectly normal thing to do IMO.

Question, I have auto-populating cells and dropdown lists for various units etc.

I know there has to be a way to do this, but does anyone know if you can modify what dropdown lists are available based on another cell's contents?

Example:

in the troop field, I select blood claws.

Now, in the squad upgrades section below that, I don't want certain things to be selectable. I.E. Banners since only Grey hunters can take banners.

Is that possible/easy/hard? Or should I just say fuck it and write (Grey hunters only) in parenthesis when I do this?

Here's a link to my spreadsheet as I have it now, pretty basic, I'm just working it out before I populate fully:

http://cid-11f2ae050fed047b.skydrive.live.com/self.aspx/.Public/SW%20List.xlsx

Birudojin

You can do something like that with names and data validation I know.

Sample setup:
Cells A1:A4 :
String 1
String 2
String 3
String 4

Select A1:A2 and give it a name of "Test 1"
Select A1:A4 and give it a name of "Test 2"

In B1, type "Grey Hunters"
In C1:
select Data->Data validation
Select "Allow "List" "
Enter the following formula: =IF($B$1="Grey Hunters",Test1,Test2)

If you then look at the values you can select, you can only select A1 and A2. If you change the string to anything else, you can select A1:A4. In your case, you'd want to add in banners and similar items in the larger named ranges which would include them, and exclude them from the others.

Making something more complicated is doable, but it can require a ton of names and means a lot of work making sure everything is organized correctly for grouping together. Add in the guaranteed exceptions, and it might be more work than it's worth

Birudojin

You could also do this with some VB scripting; a few checks on what cells contain and you could probably construct the list contents at run-time based on that. I've not done anything like that in a while however, and it does mean moving to macro functions instead of native excel commands.