Help support TMP

"Variable Excel drop down lists" Topic

7 Posts

All members in good standing are free to post here. Opinions expressed here are solely those of the posters, and have not been cleared with nor are they endorsed by The Miniatures Page.

For more information, see the TMP FAQ.

Back to the Computer Stuff Plus Board

263 hits since 3 Jan 2017
©1994-2018 Bill Armintrout
Comments or corrections?

Last Hussar03 Jan 2017 11:15 a.m. PST

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?

Personal logo Saber6 Supporting Member of TMP Fezian03 Jan 2017 11:24 a.m. PST

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

Last Hussar03 Jan 2017 3:45 p.m. PST

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!).

Personal logo javelin98 Supporting Member of TMP04 Jan 2017 1:13 p.m. PST

Here is how I did it in the past: link

Last Hussar08 Jan 2017 5:17 a.m. PST

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.

Personal logo javelin98 Supporting Member of TMP09 Jan 2017 7:25 p.m. PST

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

Last Hussar13 Jan 2017 6:02 p.m. PST

It works!

Sorry - only verified members can post on the forums.