Data Validation - How to display values in CELLS rather than drop-down list?

I'm using Data Validation/Dependent Lists to simulate a car finder web application. Make>>Model>>Trim. BUT, I don't want the choices for Trim to display in another drop-down list - How do I get the trims to display in multiple cells instead?

I tried a very simple INDIRECT(A2) function referencing the "Model" name list and made it an array so that it returns all the trims tagged as that Model. Basically it's a "Results"/"Choices" column that the array was applied to and it fills up after you choose model. The only issue is that all the unused array cells come up as #N/A and I tried error handling code {IF(ISERROR(INDIRECT(A2))),"", INDIRECT(A2)} [I also tried IF(ISNA())] but nothing worked (I have Excel 2003). Also, when there's only one value for the Model, the entire array fills up with the same one value.

Am I doing something wrong? Am I missing something? Do I need to be using something more advanced for this? I'd really appreciate any guidance!!

yea I figured that'd probably be helpful, I made one quick right here just using the first technique, let me know if you want me to include the other formulas I've tried: Example1.xls

Originally Posted by NBVC

Try:

=IF(ISERR(INDEX(INDIRECT(SUBSTITUTE($C$12," ","")),ROWS($A$12:$A12))),"",INDEX(INDIRECT(SUBSTITUTE($C$12," ","")),ROWS($A$12:$A12)))

copied down

Thank you so much, I actually arrived at the same thing on my own too once I got pointed in the right direction by others, good to know that I'm getting the hang of it!

I also got this to work based on others' suggestions: =IF(ROWS($A$1:$A1)<=COUNTA(INDIRECT($C$66)),INDEX(INDIRECT($C$66),ROWS($A$1:$A1)),"")

I have one question though....does it matter what you choose for the ROWS argument? Like you chose $A$12:$A12, but I tried both $A$1:$A1 and $E$1:$E1, and another member did $E$11:$E11, and all of them did the exact same thing. What's the purpose of that part anyway? I know it acts as a counter, but what's the significance of WHERE you define it?

Originally Posted by Fotis1991

@ the_intern

Pls don't send same question twice!

lol sorry about that, when I clicked back/refresh or something it reposted the same thing and I couldn't find a way/don't know if there is a way to delete a post