Distinct Dependent Drop Down Lists

Hello,

I would like to ask some help with a formula. I have been working on this workbook for a while now and I can't seem to get this formula to work (for the dependent list). I thought perhaps some fresh eyes would help. C7 is just a random cell I chose to place the drop down list for the. Sheet1 contains the table A3:A323 is the Asset Type(s). I am getting a #N/A error. When I look into the formula, it gets a #DIV/0 error before the #N/A. If you can see where I entered the formula incorrectly, please help.

=LOOKUP(2,1/(((COUNTIF($B$1:B1,Table1[Asset Type]))+($C$7<>Sheet1!$A$3:$A$323))=0),Table1[Asset Type])

Sample data:

Asset Type Asset
1/2 Bathroom Bathroom Sink
1/2 Bathroom Bathroom Sink Faucet
1/2 Bathroom Exhaust Fans
1/2 Bathroom Flooring/Carpet or Plank
1/2 Bathroom Hot Water Heater
1/2 Bathroom Lighting
1/2 Bathroom Paint/Wall
1/2 Bathroom Toilets
1/2 Bathroom Trim
1/2 Bathroom Vanity
AMENITIES (Line) Courtyard
AMENITIES (Line) Gaming tables
AMENITIES (Line) Grills
AMENITIES (Line) Patios
AMENITIES (Line) Pavilion

TYIA,

P

can you not use a combo box that used the range?

result of pick is stored in C7 (the index# of the pick)
C8 = the text result of C7: INDEX(A3:A323,C7)

I don't quite get what you mean. I tried using a combo box with the reference you wrote. It doesn't work.

"Reference isn't valid."

I got it, thank you for offering your solution. I was calling out, referencing the data to be dependent instead of the data depending on. A3:A323, should be B3:B323. It works flawlessly now. Again, thank you!