Data validation from main cell and sub cell

[Fluidnick]

Hello : )

I am trying to make a lot of data entry standardized in order to speed up entry and ensure consistency in the data. I am using excel 2016.

I have a list of restaurant groups (McDonalds, Costa etc) in column A which I would like as a drop down list when I click on the cell. Once I select the relevant restaurant chain I would like the cell populated. Then if I move across the B column I would like it to only show the relevant venue addresses for that chain.

I have tried to do this using Data validation but Im having trouble getting the B column to only show relevant restaurant chain addresses and not all restaurant addresses. I believe the data is best put into another sheet as the addresses may change from time to time.

Any help would be great, THANK YOU
Fluidnick

[PFDave]

you can use INDIRECT to assign the relevant named range for this.

So you will have a named range for McDonalds Say B2:B10 which contains only their addresses.

Once you select McDonalds from the drop down the next cell for addresses will contain the list named McDonalds if you apply =INDIRECT(A1) N.B assumes that A1 contains the first drop down.

If you upload a sample file I can help build this for you

[Fluidnick]

Hi PFDave,

Many thanks for the speedy response. If you dont mind sorting the formulas that would be great. I dont know what I am doing wrong and it looks so simple but just does not seem to work for me! Keeps saying 'source currently evaluates to an error'.
This is the formula I put in cell b2 to create the drop down relevant to A2 client
=INDIRECT(A6)+'VENUES SHEET'!$B$3:$B$80
Thanks for your help : )
Nick

[ChemistB]

Here's a more complex but versatile answer. I created two dynamic defined names, Restaurants and Venue.

Restaurant is fairly simple
=Sheet2!$C$2:INDEX(Sheet2!$C$2:$C$100, COUNTA(Sheet2!$C:$C)-1)

Then to pull the proper venue addresses, I first position the active cell in B2 of sheet1 (next to the first restaurant you would choose (A2))

The formula for Venue is then

=INDEX(Sheet2!$D$2:$Z$100, MATCH(Sheet1!A2, Sheet2!$C$2:$C$100,0),1):INDEX(Sheet2!$D$2:$Z$100, MATCH(Sheet1!A2, Sheet2!$C$2:$C$100,0),COUNTA(INDEX(Sheet2!$D$2:$Z$100, MATCH(Sheet1!A2, Sheet2!$C$2:$C$100,0),0)))

Modify your end cell (Z100) to properly represent the maximum number of restaurant names and venues you will have (i.e. M500 if you expect a max of 13 restaurants with up to 500 addresses)
See attachment.

[PFDave]

Hi Fluidnick,

Does this achieve what you are looking for?

If so I can explain in a bit more detail, and we look at a dynamic range for the restaurants.

[Fluidnick]

Hi.
Thanks works perfect thank you : )
Ive copied the sheet across to my excel file and it works great. Many thanks for your help on this one!
Nick

[PFDave]

No worries Nick, glad I could help.

If that takes care of the issue in this thread please do mark as solved and add any reputation which you see fit