Creating thousands of Named Ranges for Dependent Validation

Okay, this one has lots of issues. Sample attached. What I'm trying to achieve is to allow my users to select a position to do something with. For each Heading in my sample, I want to allow my users to use Data Validation to pull up the position numbers under that heading. I want to do it using named ranges, so need to name each column with the header name. My Sample is small; there are actually hundreds of Headers, I've just shown a few.

To bring in the position numbers under the headers, I'm first doing a Pivot of Header and Position. Next i'm copying the header range using the Advanced filter to get just the Unique values, then pasting Transpose to get them in a row. Next, I do a Vlookup to bring in the position numbers, and use IFerror to replace "" if nothing is found. Next I copy/paste as values to wipe out my formulas.


So, problems so far:
1) My position numbers have a bunch of "blanks", and I want to remove all the blanks.
2) The blanks aren't blank. When I GoTo Special, "Blanks", no blanks are found.
3) I'm using the macro below to create my named ranges, but even though the named ranges are made they aren't showing up for my data validation (using "Indirect(A1)". If I delete the named range as set up by the macro, and instead select my position numbers in the column, and name that selection with my Header name, the data validation Does work. So, my macro isn't doing what I need it to do.

Any help with this would be greatly appreciated.

Please Login or Register  to view this content.

Okay, I've solved the first two issues by following the instructions below. Now still need help with the last issue.

To delete all of our blanks and sort the Position Numbers to the top:

You can use “Find” to find all the values that are empty (even if they are results of formulas).
1) Select the used range
2) Press CTRL+F to open the "Find" dialogue
3) Clear any FIND text
4) Click on “options”
5) Select “look in values”.
6) Just hit “Find all”.
Result: Excel finds all cells that have empty values.
7) Select all the matches from FIND list (use shift to select)
8) Close the find box.
9) Delete the cells using CTRL+- (hold Control, hit Plus, then Minus)

Figured a way to resolve the last issue. Basically just recorded "Cntrl-Shift-F3", which creates a named range out of your selection. Tied that to a loop through all columns in my range. Works great on my sample, haven't tried it on the real project yet.

Please Login or Register  to view this content.

I feel like I've been talking to myself in this thread, but figured I'd close the loop anyway. We wound up creating almost 3000 named ranges for this exercise. The macro above worked well to create all of our named ranges. Stuff discovered/developed during the process:

To create the named ranges we wanted to remove all of the illegal characters, so made judicious use of the "Substitute" function. See picture for what we did.

To create the lists of entries under the named ranges, we had to use a VLookup, and for empty values, instead of putting "" we put a "d" (D is easier to work with than blanks). When we were all done, we had to delete the 'd's. The sub below did that very well. That one, with a minor change to allow the input of any value, is going into my toolbox, along with the named range creator. if anyone is reading this post, I hope the macros in it are of use to you.

Please Login or Register  to view this content.