Dropdown List values based on a IF filter
Hi,
I have an Invoice Excel file with 2 sheets with Dynamic Microsoft CRM Online query data, one is for Accounts, another for Contacts.
On the invoice there is a simple drop-down list to select the Account, and my objective is for a second drop-down list to list all contacts from the selected account. I know this would be possible with static named ranges, but both Accounts and Contacts are dynamic.
Any suggestions on how this can be achieve?
I have an Invoice Excel file with 2 sheets with Dynamic Microsoft CRM Online query data, one is for Accounts, another for Contacts.
On the invoice there is a simple drop-down list to select the Account, and my objective is for a second drop-down list to list all contacts from the selected account. I know this would be possible with static named ranges, but both Accounts and Contacts are dynamic.
Any suggestions on how this can be achieve?
Subodh Tiwari (Neeraj)
Pls post a small sample workbook.
Can you put the query list information onto a hidden sheet and define the named range as the entire column: i.e., "A:A" instead of "A1:A15"? Then when you set up the data validation for the dropdown just select "Ignore blank cells" and define your dependent ranges like normal. That should take care of the variable data set lengths.
You would be better to use Dynamic Named Ranges that will adjust to the amount of data in each column
I use an Excel Table, defining the names from the Table Columns, see attached example.
You then use an INDIRECT formula in the second Data validation. Attach a simple example with some data and I'll see if this method would work for you.
EE--Cascading-Validation-1-.xlsx
I use an Excel Table, defining the names from the Table Columns, see attached example.
You then use an INDIRECT formula in the second Data validation. Attach a simple example with some data and I'll see if this method would work for you.
EE--Cascading-Validation-1-.xlsx
ASKER
Hi sktneer, I have sent you the file on a Private Message.
Thanks for the suggestions Russel and Roy_Cox, I will review them ASAP.
Thanks for the suggestions Russel and Roy_Cox, I will review them ASAP.
Why not provide a sample workbook for all to see
ASKER
Hi Roy,
For me to be able to create a sample workbook that has the desired functionality while protecting customer privacy I would need to create a sandbox CRM environment, and unfortunately I am currently overwhelmed with work. I can share in a PM a sample file but it does have some private data that's why I prefer to keep it restricted to PMs.
For me to be able to create a sample workbook that has the desired functionality while protecting customer privacy I would need to create a sandbox CRM environment, and unfortunately I am currently overwhelmed with work. I can share in a PM a sample file but it does have some private data that's why I prefer to keep it restricted to PMs.
Sorry IAAS!
I haven't been able to look at your file yet as I am really busy in a project and don't think will be able to look at it for few more days or so.
So it would be better if you can share the file with Roy so that he can look at it and suggest a solution accordingly.
Or you can create a dummy sample workbook with desensitized data and upload it here so that more member will have access to your file and may offer a solution here.
Hope you understand.
I haven't been able to look at your file yet as I am really busy in a project and don't think will be able to look at it for few more days or so.
So it would be better if you can share the file with Roy so that he can look at it and suggest a solution accordingly.
Or you can create a dummy sample workbook with desensitized data and upload it here so that more member will have access to your file and may offer a solution here.
Hope you understand.
Sorry IAAS!
I haven't been able to look at your file yet as I am really busy in a project and don't think will be able to look at it for few more days or so.
So it would be better if you can share the file with Roy so that he can look at it and suggest a solution accordingly.
Or you can create a dummy sample workbook with desensitized data and upload it here so that more member will have access to your file and may offer a solution here.
Hope you understand.
I haven't been able to look at your file yet as I am really busy in a project and don't think will be able to look at it for few more days or so.
So it would be better if you can share the file with Roy so that he can look at it and suggest a solution accordingly.
Or you can create a dummy sample workbook with desensitized data and upload it here so that more member will have access to your file and may offer a solution here.
Hope you understand.
IAAS, I have your file and will take a look later
ASKER
Cheers, thanks
I've looked at the workbook. This can be done but it would need VBA to achieve it.
If this is acceptable let me know and I'll write some code.
If this is acceptable let me know and I'll write some code.
ASKER
Hi, yes it would. thanks
I'll work on it after work this evening
I have returned your workbook with the code
In Factura select a Client the ATT is automatically populated with a list of contacts
In Factura select a Client the ATT is automatically populated with a list of contacts
ASKER
Hi Roy,
That is great thanks.
Is it possible to have also the remaining data from the contacts and not just the name? the objective is to vlookup those additional details such as phone and email when a contact is selected, and by doing the vlookup on the already filtered contacts the chance of getting contacts with the same name is drastically reduced.
That is great thanks.
Is it possible to have also the remaining data from the contacts and not just the name? the objective is to vlookup those additional details such as phone and email when a contact is selected, and by doing the vlookup on the already filtered contacts the chance of getting contacts with the same name is drastically reduced.
I'll extend the data that is copied.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Roy, it works perfectly thanks a lot ;)
Pleased to help