"Variable Excel drop down lists" Topic

OK, this is quite a difficult thing for me to Google, as what I'm asking is a complex question.

In Excel you can do drop down lists in the data validation. Part of this is instead of listing the individual items in the dialogue box for the Validation, you can specify a range, and the menu will pick up from this, amending itself as the list is amended.

However I have a drop down box which can change depending on the option entered else where.

Thus if the user picks 'British' from the fleet box (why yes, it is a force calculator, thank you for asking) I want the list of units to come from range (say) A1 to A13, but if they pick French they come from A14-A26.

Is there a way to get the validation box to change the range it is looking at?

I think you might need to write/record a macro for this. I know that you can control the properties of an object in VisualBasic for Applications (VBA). Basically a SUMIF scenario

What I've done is on the Ship Data tab I've called each ship
British1, British2 etc in Column A, with actual type and stats after this.

I then have another tab called 'selected data'. In it I have column A concatenates the country with this the other part of the data line name (+A1&1)

Vlookup then pulls the Unit data onto the 'selected data' sheet using this combined name.

The Fleet Builder sheet – the bit the user makes his selections on – then populates the drop downs from this range on the Selected Data sheet, and uses VLOOKUP on the drop down (which is the Ship Rate) to pull over the actual stats. The reason for this way round is not all countries have the full range of Rates, and they all have 'personality ships' (Victory, Constitution etc), as well as different Admiral and Captain types (eg the French get Revolutionary, which is hardly going to go down well with the Admiralty in London!).

Here is how I did it in the past: link

Javelin – may well be exactly what I wanted! Will investigate it further. The problem with programming/functions/etc is that I find I know the computer can do it, but don't know the command that I need.

No problem, LH! Let me know if you need any further help on that! You can always drop me a line at javelin98 AT lycos.com.

It works!