dynamic Form List Boxes

[freud1]

Hi Guys,

any ideas on this would be appreciated,

Im designing a form which will be a few list boxes that input data into specific cells in another sheet.

I would like the list boxes to change depending on selection.

eg: if there is a certain value selected in the first list, then only the relevant values will appear in the second box.


so if there are values Potato, Banana and Apple in list one, and list two contains values White, Yellow and Red.

if banana is clicked in list 1 then only yellow will appear in box 2 as a selection.



the only way i can think of doing it is with the list box linked to a group of cells which each contain a very long IF formula so if there are easier ways that would be great!

thanks

[freud1]

ok guys sorry found the solution, using this formula in the cell reference

=INDIRECT(SUBSTITUTE($A$1," ","_"))

[freud1]

sorry to mess you round,

haven't actually found the solution i was looking for,

this formula is fine for data validation on a standard cell but not for form lists, any ideas again?

[rylo]

Hi

What sort of a "form" do you mean? A userform raised by a macro, or a place on the spreadsheet that is formatted to look like an input form?

rylo

[freud1]

hy rylo,

its one of the lists available on the "Forms" toolbar, alongside combo box and buttons?

thanks

[rylo]

Hi

Can you please attach an example copy of your workbook so we can see what you are struggling with.


rylo

[dlroo]

Using if statements in a table might be a good way to go if you dont have that many choices. but if you have multiple choices per selection it would get pretty difficult. What scale are you talking about?

Thanks
Dlroo

[dlroo]

Or you can create a list with all your primary and secondary selections such as

Primary | Secondary1 | Secondary2 | Secondary3

and repeat this for each primary you have.

Then creat another list using vlookup. This one be a single column list and you use the primary selected as your reference in the vlookup. If the number of secondaries is not the same for each primary thats all right, just create as many vlookups as secondaries you have.

now create another drop down list with data ---> validation -------> list that uses the table you just created using the vlookups.

try that out

[freud1]

hi guys,

hope this demonstraits what I mean.

thanks for the help

[rylo]

Hi

You don't need form items for this. Using your example file, select cell E8. Data, validation, list, source: =$A$1:$D$1

Now go to G8. Data, validation, list, source: =INDIRECT(E8)

Make a selection from E8, and the only available options in G8 will be for that item.


rylo

[freud1]

hi rylo and dlroo,

thanks for the advice, I know how to do this using Data Validation --> List, just wasn't sure if excel allowed for the other list boxes to run a similar process.

might be a whole lot easier for me to just stick with normal cells and use the validation though