Data Validation Linking Lists Together

Dear All,

I have used this forum a couple of times before and it has proved to very helpful. I have a problem which is beyond me and was wondering if someone could point me in the right direction.

I would like the functionality in a cell so when you click on it you can choose from a list of options. I have already sussed this and have done it using data validation. The problem i am having is when selecting the data to go in the list box, i can only manage to make this happen if i select data of the same worksheet. i have tried the following syntax but nothing seems to work.

'sheet2'!A2:A47
sheet2!A2:A47
"sheet2"!A2:A47

The second problem i am having which i was wondering if anyone could shed some light on is. Once i select a value from my list, depending on the value the selected the values in a second list box alongside are altered. I have tried to show a working example below.

List A List B
Apple Yellow
Banana Green
Grape Red

So if i were to choose apple from List A only green and red remain in list b.

Is this possible?

Many thanks in advance for all your help.

Andy

To resolve both of your problems, you will have to use named ranges.

1. You want to use data validation to refer to a list on a different worksheet: Simply highlight the list that you want to use as a validation. While it is highlighted, press CTRL+F3, which will bring up the "Define Names" dialog. Name the range as you please. Next, go back to your first sheet. Highlight the cell that you want to contain the validation. Click on validations, and in the list box, type =(NAME YOU CHOSE). for example, if you named your range LIST1, in the list box type =LIST1

2. This is a bit more in depth with named ranges. I will attach a link that should help you with this. (Be forewarned, it is somewhat complex).
http://www.contextures.com/xlDataVal03.html