Drop Down List through Formula

[Noremacs]

Hello Everyone,
I'm trying to create to display a drop down list in a cell if the data in another cell does not meet certain criteria. Here is the formula I currently have written.

=IFERROR(IF(OR(A8="Dog",A8="Cat",A8="Bird"),B18,Animal_List),"")

The cell displays the information from B18 appropriately for Dog, Cat, and Bird. However, when something other than those three is entered it does not provide the list from which to choose. Any ideas would be greatly appreciated. Thank you very much for your assistance and consideration.
Sincerely,
Noremacs

 

[sergioMabres]

I have tried to do some test and I think that all reference inside validation source should be $A$8, and $B$18
Regards
Sergio

 

[Noremacs]

I have tried to do some test and I think that all reference inside validation source should be $A$8, and $B$18
Regards
Sergio

Thank you for your prompt response Sergio. I added the $ in the appropriate locations and it provided a slightly different result, but didn't provide a drop down list. Let me be a little more descriptive in what I'm trying to accomplish, what I'm getting, and where my stumbling block seems to be.

My goal: I'm having individuals pick an animal in cell A8. If the person selects Cat, Dog, or Bird then my target cell (B8) auto populates with the number from B18. If the individual picks something other than Cat, Dog, or Bird then I want a drop box to be placed in B8 that will allow the indiividual to pick a number between 1 & 9.

My current formula: =IFERROR(IF(OR($A$8="Cat",$A$8="Dog",$A$8="Bird"),$B$18,Animal_List),"")

My results: When Cat, Dog, or Bird is used in A8 the number from B18 is displayed appropriately in B8. When A8 is left blank then B8 is left blank as intended. However, when A8 has something other than Cat, Dog, Bird, or Blank the number that is in the 8 row of the drop down list is displayed rather than the drop down list (Animal_List). Animal_List is simply the numbers 1-9 + a blank space located in cells H2:H11. As stated this returns a value of 7 in B8 rather than a drop down box.

Does this better explain where I'm having trouble? Is there a way to have a drop down box placed in a cell based on the results of another drop down list. I know this seems a lot like doing a dependent validation, but I want either a forced result OR a drop down box rather than a drop down box already there that changes depending on another cell.

In typing that out I just realized that perhaps I could add the numbers from B18 and force a choice from the drop down box into the cell if Cat, Dog, Bird, or Blank is chosen... I'll work on that in the mean time.

 

[sergioMabres]

I have seen this problem before I like very much this solution, there is an article here Excel Data Validation -- Dependent Lists you can read to have an idea about filtering the values shown in a select list.
1. You can use a formula see this Excel 2007: Data Validation list with filtered data - Stack Overflow
2. You can use a range name and change with a macro the content of the range name, see this http://www.mrexcel.com/forum/excel-...data-validation-list-based-another-entry.html
I hope these articles can be of help for your problem
Sergio