Data Validation List "Source" field Syntax Trouble

Help! I've been failing all day to create a "Documents" drop-down Data Validation List where the list changes based on whichever value a user first selected from a "Client Name" drop-down list in another cell.

My users select the Client Name from a named list that's familiar to them day-to-day, so its values have spaces (i.e.: The McGregor Corporation, etc.), but because Excel names for named ranges do not allow spaces, I have to use a 2 column table for changing each client's CommonNames to NoSpacesNames. I've created each client's unique Documents range with the NoSpacesNames as the name (McGregor, etc.). So I should be good to go...or so I thought at first.

Problem is, I think, what the Data Validation List "Source" field allows as valid syntax. I've tried both an INDEX and VLOOKUP formula in the Source field to get it to present the user with the correct Document list. Both technically WORK as correct Source field entries, but when I click the Documents drop down, it just presents me with the NoSpacesName, not the named range I was hoping for. I tried putting a "Value" function in front of the INDEX and VLOOKUP, but that gave me formula errors.

Here's the Vlookup Formula I'm using (where A2 is the first validation list choice): =VLOOKUP(A$2,FormsMatrix[[CommonNames]:[NoSpacesNames]],2,FALSE)

As always, the answer is probably staring me in the face, but I'm too frustrated at this point to see it...

Thanks!

One more thing: Every client's NoSpaceNames list of document names is made up of the same document types, only some clients have all documents, others have different selections of the complete set. So I anticipate when this works that I'll see spaces in my drop downs where not-applicable document types would have gone. How do I get rid of the blanks to present cleaner, shorter lists? I clicked "Ignore blanks" on the setup window, but it doesn't fix that apparently...

Without any example file (xlsx not a picture) is hard to say something, so...

Read here Create Dependent Drop Down Lists

Yes, I was afraid of that. To post the .xlsx document I'll have to first replace HIPAA-sensitive data with dummy data. Was hoping I had explained well and simply enough someone would be able to see it in their mind. Oh well. I'll try to post it in a bit. Thank you.

Instruction

Attach a sample workbook. (Not a picture!)
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook

if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Note:
Please do not attach password protected workbooks/worksheets
Please do not attach file(s) from exterior servers
Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

see attached

I made mistake before with attachment. Not attachment from right person
I will look at yours, Quinn.Farley

1. Change your e.g. UNIQUE_OIS with formula to NAME (UNIQUE_OIS) of range.
Like I said in previous post you can see dynamic ranges if you will use Excel Table and NAME of range
I did it and it works well. Much easier
Try yourself

Fantastic!!!! For someone with a slightly demonic username, you are an angel!

I'll let you know how it goes very soon...

@JohnTopley

LOL John, thank you

INPUT sheet
1. Cell A3...: Full Client Name is a main list(s) Correct. Currently, I have that as a Named Range, but could make it into a dynamic table with no real change here.
2. Cell B3...: dependent list from A3 (INDIRECT(A3) Though it's not a list (I included it only to visualize steps taken to get to the drop downs), you are correct that it's dependent on A3. I use a vlookup in my example, but for the actual document I use index/match because our client list isn't presented alphabetically, it presents clients by "most active" status)
3. Cell C3...: dependent list from B3 (INDIRECT(B3) All drop down lists in column C, D, and beyond (actual document has the same drop downs through Column M and all dependent on the values in B.)
4. Cell D3...: dependent list of ??? (the same like above INDIRECT(B3) ?) See above.

For me, my problem all boils down to this: Why can't a Data Validation list "Source" formula use an formula like "=INDIRECT(B3)" where B3's value is the name of a dynamic, named range (say, "UNIQUE_OIS")? If I use the formula "=INDIRECT(UNIQUE_OIS)", it works just fine.

For the longest time, I thought it was a simple syntax issue in the "Source" formula, but nothing has worked.

It will take a little time
Be patient

There's absolutely no expectation on my end. I'm already so grateful for the attention you've given me. Thank you.

See attached:

If this solves your problem, I'll fill in the blanks i.e explain what I have done..

Something like that?

Sandy,

I see what you did there on the Quinn.Farley dependent lists file. You pre-populated all drop down fields with the "---SELECT FORM---" value so when selecting the drop down it went to that value first. I like doing that because if when there are blank values in the named range, it doesn't default to that first. I took it further: After changing all drop down forms field cells to white, I applied a Conditional Format rule for them all to use black font color when the cell's value wasn't "---SELECT FORM---". Even applied a light cell fill color to further the effect. Works great! Thank you!

However, your solution also rigidly defines the range of each client's unique documents lists. That's the part I'm required to keep dynamic since they change regularly. And that goes back to the heart of the problem -because it's required to be a dynamic named range, I'm having the problem getting Indirect to refer to it.

Sandy,

I see what you did there on the Quinn.Farley dependent lists file. You pre-populated all drop down fields with the "---SELECT FORM---" value so when selecting the drop down it went to that value first. I like doing that because if when there are blank values in the named range, it doesn't default to that first. I took it further: After changing all drop down forms field cells to white, I applied a Conditional Format rule for them all to use black font color when the cell's value wasn't "---SELECT FORM---". Even applied a light cell fill color to further the effect. Works great! Thank you!

However, your solution also rigidly defines the range of each client's unique documents lists. That's the part I'm required to keep dynamic since they change regularly. And that goes back to the heart of the problem -because it's required to be a dynamic named range, I'm having the problem getting Indirect to refer to it.

If you want dynamic list simply select every column (range in column) on List sheet, go to INSERT click Table and now you will be able to add more items under existing items.
Don't do it for all columns at once, but one by one
If you want blank field (cell) simply delete "select form" and you will get blank.
Do you want next attachment with corrections?

I should mention that a given user will field dozens of forms requests daily, but must treat each request, even if it's for the same client, separately -with its own row. This is because other fields I didn't include say where to send that request's forms to, and they're always different addresses and departments within the client's organization. I'd like to have the form as Sandy presented it -with client rows already defined and as many drop down fields as needed out to the right, but that solution, though awesome, won't work for me.

Yes, I'd love to see what more you've come up with. Applying all this on my end as well. Thank you so much.

As you wish

I love the embedded substitutes within the INDIRECT "Source" field formula. Smart. The question remains though, if the resulting text refers to a dynamic, filtered-to-unique-values range like mine, does it still work?...