May 22 2020 03:35 AM
I am trying to use VLOOKUP which works but I want to know if there are multiple results in the table_array can it combine with data validation. I can get them both working but not together.
In data validation source i have put =IF(B11="Bristol 121",'Data Sheet'!$B$8:$B$15)
For the Vlookup I have put =VLOOKUP(B11,Table10[#All],2,FALSE)
Basically if Bristol 121 is selected then it will add a drop list for the contacts for that site. on the front page.
I have attached a test sheet for an example
May 22 2020 08:37 AM
Mark
Not quite sure what you are trying to do here?
The dropdown list for the Contact seems to work ok i.e. if Bristol 121 the site then the correct list of names appear.
Are you looking to populate other fields as well on the front page.
BTW - what if Bristol 121 is not selected - what results would you like then?
I attach an updated sheet - but not at all sure this is what you are looking for.
Please clarify.
thanks,
Peter
May 22 2020 09:18 AM
Hi Peter
Thanks for coming back to me on this. Unfortunately I had to strip out most of the info from the sheet to share online so it's not a great example.
Basically on the front sheet when you select the client it adds the contact and address etc. The trouble I have is one of our clients have about 5 contacts. I have been using VLOOKUP which works fine for all the sites with only 1 contact. Bristol121 however will only show the top result in the lookup.
I want to know if only when more than one result they can appear in a drop down list in the contacts cell? I added a =IF(B11="Bristol 121",'Data Sheet'!$B$8:$B$15) in the data validation but it overwrites the VLOOKUP formula rather than work off the back of it. (I may be using the wrong formula but my knowledge is basic.
May 22 2020 12:18 PM - edited May 22 2020 12:25 PM
Assuming you can rely on the Table to be sorted by Client, you could set up a named formula, (Formulas\Names Manager
Name: ContactList
Scope: Workbook
RefersTo: =INDEX(Table1[Contact],MATCH(B11,Table1[Client],0)):INDEX(Table1[Contact],MATCH(B11,Table1[Client],1))
For your Data Validation, select List and Source: =ContactList
Data validation doesn't seem to like having structured table references in it's Source, so there's an added step of using a named formula.
May 22 2020 12:33 PM
May 23 2020 04:23 AM
Hi Mark,
is this a complete list of your clients/contacts or just a subset?
For example, is it likely that you will add another client who might have (say) 3 contacts?