Two-column-based validation without names

[jorondo]

Hi,

I have searched the web for long for an answer and haven't found one yet.

Essentially; I have a table with two columns, «*Beer*» and «*brewery*». Every beer occurs once, but breweries many times each.

On another sheet, I have one cell with a validation list of all of my brewers; what I can't figure out is how to make another cell taht would have a validation list based on the list of beers from that brewer. The list will change with time (by adding beers to the data set), which is why I do not simply make a static list of all the beers per brewer, and why I can't use names to make dependent validation lists since the name ranges will be changing.

Is there any way to simply make the validation of the Beer name conditional on the name of the brewery being selected, preferably without use of VBA because I'm not quite there yet, technically speaking?

Thanks.

[hemesh]

Hello and welcome to the forum

Please attach a sample book.

go to --> advanced---> select paper clip icon or manage attachment

[jorondo]

Here is a sample.

[hemesh]

go to formula name manager select define name. Give name as listing or whatever you want and in refers to add this formula
=INDIRECT("BRASSEURS!B"&(1+MATCH(Sheet1!$A2,Table3[Brewer],0))&":"&"B"&(1+MATCH(Sheet1!$A2,Table3[Brewer],0)+COUNTIF(Table3[Brewer],Sheet1!$A2)-1))

on second sheet in A2 type the name and in b2 add data validation select list and in refers to box add =Listing or the name you have given

as you are using table so this will be dynamic based on table.

[jorondo]

wow... thanks. This is exactly the functionality I have been looking for. However when I adapt the formulas to my actual workbook I get the wrong beers by brewer.

On Sheet1 is where I want the validation to be (B3 is validated with Listings)

My listings formula goes:

=INDIRECT("DATA!C"&(1+MATCH(DASHBOARD!$B$6,Table3[Brasseurs],0))&":"&"C"&(1+MATCH(DASHBOARD!$B$6,Table3[Brasseurs],0)+COUNTIF(Table3[Brasseurs],DASHBOARD!$B$6)-1))



Any idea why?

[hemesh]

You have matched in the last table that was Table3 it is supposed to be matched at Table1
=INDIRECT("DATA!C"&MATCH($B$6,Table1[Brasseur],0)+1&":C"&MATCH($B$6,Table1[Brasseur],0)+1+COUNTIF(Table1[Brasseur],DASHBOARD!$B$6)-1)

Hope this helps

[jorondo]

It worked. Thank you so very very much. I have spent so many hours trying to figure all of this out, your help is much appreciated!

cheers
Jonathan

[hemesh]

Glad ! I could help !

if your query is solved you can mark thread as solved go to thread tools and mark as solved.