Data Validation based on list & sort & separate

[Thunderer]

I've been wracking my brain for the past day and a half trying to use formulas initially (INDEX, MATCH, VLOOKUP), and finally realized the best way was using VBA. I have attempted at writing it however I am coming up short on the results and coming on with a headache

Basically, I have a list of names in column 'Main'!O5. I want to copy ALL names from 'Incident Data', 'Change Data', & 'Task Data'. Paste those names to 'Calc'!B8, remove duplicates, sort those names so that the validation list from 'Main'!O5 is pasted between 'Calc'!B8:B35 and all other names and not on the validation list and any cells left blank are pasted to 'Calc'!B40 and on.

I believe I can get the rest of the sorting I need done on my own, however I can't seem to get this to work.


Can anyone help a novice by providing a little direction?

I appreciate the help and love these forums. I just can't find what I am looking for.


Thanks,

Tim

[mikeTRON]

Post the sample data set.

Also, why bother with VBA when you can have a dynamic named range on a pivot table. you can use that dynamic named range for the data validation.

[mikeTRON]

I am not sure what you were getting at with all the cell references but here is what I am basically talking about. I also like to have a DATA tab of some sort, then a list tab, then some sort of reporting or summary tab where everything is pretty.

Take a look at the dynamic named ranges for each of the components as well as the data range for the Pivot Tables themselves, which allows you to simply drop the data in and have it automatically update the range, so you can simply refresh all pivots and you are good to go.


Dynamic Named Ranges and Data Validation (with pivots).xlsx