OFFSET / Data validation: drop down list not working

[201208Steph]

Hi all,

I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.

{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}

The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.

Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Any suggestion would be much appreciated!
Thanks
Steph

Table TMI:

A B C D[TABLE="width: 387"]
<colgroup><col style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;" width="112"> <col style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;" width="142"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" width="78"> <col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;" width="93"> <col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;" width="89"> <tbody>[TR]
[TD="class: xl67, width: 112, bgcolor: transparent"]Client[/TD]
[TD="class: xl67, width: 142, bgcolor: transparent"]Account[/TD]
[TD="width: 78, bgcolor: transparent"]NAM[/TD]
[TD="width: 93, bgcolor: transparent"]Contact[/TD]
[TD="width: 89, bgcolor: transparent"]Comments[/TD]
[/TR]
[TR]
[TD]Chi[/TD]
[TD="class: xl67"]Baa1[/TD]
[TD]GD[/TD]
[TD]Marie [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Chi[/TD]
[TD="class: xl67, bgcolor: transparent"]Boo0[/TD]
[TD="bgcolor: transparent"]GD[/TD]
[TD="bgcolor: transparent"]Melisa [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Chi[/TD]
[TD="class: xl67"]Boo0[/TD]
[TD]GD[/TD]
[TD]Mel [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Chi[/TD]
[TD="class: xl67, bgcolor: transparent"]Boo0[/TD]
[TD="bgcolor: transparent"]GD[/TD]
[TD="bgcolor: transparent"]Adam [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Chi[/TD]
[TD="class: xl67"]Sains[/TD]
[TD]PWT[/TD]
[TD]James [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Chi[/TD]
[TD="class: xl67, bgcolor: transparent"]Sains[/TD]
[TD="bgcolor: transparent"]PWT[/TD]
[TD="bgcolor: transparent"]Louise [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Chi[/TD]
[TD="class: xl67"]Sains[/TD]
[TD]PWT[/TD]
[TD]Gill[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Chi[/TD]
[TD="class: xl67, bgcolor: transparent"]Tacco[/TD]
[TD="bgcolor: transparent"]RR[/TD]
[TD="bgcolor: transparent"]Andrew [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Chi[/TD]
[TD="class: xl67"]Tacco[/TD]
[TD]RR[/TD]
[TD]Dean[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Chi[/TD]
[TD="class: xl67, bgcolor: transparent"]Olive[/TD]
[TD="bgcolor: transparent"]NG[/TD]
[TD="class: xl67, bgcolor: transparent"]Sam[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Emco[/TD]
[TD="class: xl67"]Baa1[/TD]
[TD]GD[/TD]
[TD]Christina [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Emco[/TD]
[TD="class: xl67, bgcolor: transparent"]Olive[/TD]
[TD="bgcolor: transparent"]NG[/TD]
[TD="bgcolor: transparent"]Rob [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Emco[/TD]
[TD="class: xl67"]Olive[/TD]
[TD]NG[/TD]
[TD]Joe [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Trade[/TD]
[TD="class: xl67, bgcolor: transparent"]Sains[/TD]
[TD="bgcolor: transparent"]NG[/TD]
[TD="bgcolor: transparent"]Clifford [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Trade[/TD]
[TD="class: xl67"]Sains[/TD]
[TD]NG[/TD]
[TD]Matthew [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Trade[/TD]
[TD="class: xl67, bgcolor: transparent"]Tacco[/TD]
[TD="bgcolor: transparent"]RR[/TD]
[TD="class: xl67, bgcolor: transparent"]Bob[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Table with offset function:
A B C D[TABLE="width: 246"]
<colgroup><col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" width="73"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" width="55"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;" width="109"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" width="90"> <tbody>[TR]
[TD="width: 73, bgcolor: transparent"]
Chi[/TD]
[TD="width: 55, bgcolor: transparent"][/TD]
[TD="width: 109, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]NAM[/TD]
[TD]Contact name[/TD]
[TD="class: xl67"]Comments[/TD]
[/TR]
[TR]
[TD]Boo0[/TD]
[TD]GD[/TD]
[TD]Melisa [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Baa1[/TD]
[TD="bgcolor: transparent"]GD[/TD]
[TD="bgcolor: transparent"]Marie [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Olive[/TD]
[TD]NG[/TD]
[TD]Sam[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sains[/TD]
[TD="bgcolor: transparent"]PWT[/TD]
[TD="bgcolor: transparent"]James [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Tacco[/TD]
[TD]RR[/TD]
[TD]Andrew [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

All client, NAM and account lists are dynamic !

 

[Andrew Poulsom]

Welcome to MrExcel.

What would your formula look like without using structured references? Have you tried defining a name that refers to the formula and using that name in data validation?

 

[201208Steph]

Welcome to MrExcel.

What would your formula look like without using structured references? Have you tried defining a name that refers to the formula and using that name in data validation?

YESSS it's working !
I have defined a name "GreatIdea" that refers to the formula and I have used that name in data validation!

Thank you, MrExcel is great !