"Variable Excel drop down lists" Topic
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
|Last Hussar||03 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?
| Saber6 ||03 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 Hussar||03 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!).
| javelin98 ||04 Jan 2017 1:13 p.m. PST|
Here is how I did it in the past: link
|Last Hussar||08 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.
| javelin98 ||09 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 lycos.com.
|Last Hussar||13 Jan 2017 6:02 p.m. PST|