data validation and indirect

[jang430]

Hello All. Please help with my question.

Objective: To have B column have a list of choices depending on answers gotten from column A.

I have column A with a specific set of answers on a list (data validation)

I want column B to depend on the answers of column A, and give another set of choices

I created named ranges, e.g. def (apple, mango, banana)
ghi (straberry, melon, durian)
jkl (pomelo, pear)

I selected entire column B, and selected Data Validation. I selected List, then under source, I put in "=indirect(A1)"

But this formula is not give you the choices depending on answers on column A cells. Any ideas?

[Domski]

Hi,

There's a good explanation of how this should work here:

http://www.contextures.com/xldataval02.html

Dom

[bentleybob]

Not sure where you went wrong. Attached is an example exactly as you describe (except I only applied it to A4 and B4), and it works fine.

[jang430]

Thanks Domski, How I learned to do this, I learned from the site you provided :-) But some info is lacking, hence my questions.

[jang430]

Hi Bentleybob,

Thanks for taking the time out to doing that sample. Yes, you've shown it can be done. I've reached the same conclusion as yours. The difference though from your excel file is, I have entire column A with Data Validation right? I want column B answers (choices) to be dependent on column A choices. Can you try doing Data Validation on entire column B? Instead of doing it just for one cell? What is the formula you have to use? =Indirect(A:A)? If you do the formula I've shown, cells in column B no longer show choices.

Thanks.

[bentleybob]

I'm assuming that you want each cell in Column B to be dependent on the selection in it's counterpart cell in Column A (e.g., B1000 depends on the selection in A1000). If so, just copy the range A4:B4 down as far as you like.

If that's NOT what you want, then please be more specific. What would B1000 depend on?

[jang430]

Yes, you are correct bentlebob. If I'm not mistaken, copying a formula gets erased. As much as possible, I'd like to have the formula embedded. And so that I can be sure all cells have the same formula. Is there a way?

[bentleybob]

Not sure what you mean by "copying a formula gets erased". I simply copied the formulas down through row 1000 to show you nothing gets erased; the validation (drop-downs) gets copied too. Pick any cell from A4 to A1000. Select something from the dropdown. Then go to the cell next to it (in the same row)., The dropdown there will be dependent on the results of what you picked in column A.

Sorry, but if I'm missing your point you need to be more explicit. Perhaps give me an example of what you want that this doesn't do.

[jang430]

Bentleybob,

I guess you're right. I saw your excel and it worked. Last question though, instead of dragging it all the way to the most number of cells, Is there a way I can select the whole column, and say the formula is =indirect(B:B)?

Just a thought.

Either way, thanks for demonstrating this. I can already proceed as it is.

[bentleybob]

No, you can't put that into the "source" field for data validation, so you're stuck copying down.

[jang430]

Thank you Bentleybob. You've just ended my hours of searching for an answer :-) May I say, definitively.