Dynamic Data Validation List Based on Another Column

[crispycook]

Any ideas on a non-VBA option to have a data-validated dropdown list on Sheet 1 be dynamic, using the value in a neighboring column in Sheet 1 to then lookup the dropdown list values from sheet 2? I really appreciate any ideas!

Sheet 1
I will populate the values in the Main Category column and then want the Sub Category column to have a dropdown that only shows options that correspond to the Main Category, based on all corresponding Sub Category rows from Sheet 2. Other Data column will all ready be populated, so I'm effectively adding metadata to Sheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Main Category[/TD]
[TD]Sub Category[/TD]
[TD]Other Data[/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
For each specific Main Category in this sheet, all Sub Categories will be unique, but different Main Categories may use some of the same Sub Categories. Both columns will be manually entered. Additional rows will be added over time and this sheet needs to be structured this way, rather than having the Main Categories on one axis and the Sub Categories on another axis.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Main Category[/TD]
[TD]Sub Category[/TD]
[TD]Other Data 1[/TD]
[TD]Other data 2[/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]SubValue 1[/TD]
[TD]2300[/TD]
[TD]2100[/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]SubValue 2[/TD]
[TD]500[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD]SubValue 3[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD]SubValue 2[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]SubValue 4[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]

 

[crispycook]

Figured it out here: Excel Data Validation Drop-Down using a Lookup - Stack Overflow, took a while to figure out what to search by.

Thanks to anyone who started to reply