Extracting conditional data from different sheet

Okay, Try the attached, I would post the formulas, but the one is pretty big, it allows for 20 responsibilities
I would probably make that a named formula normally, or use VBA for the concatenation, but you need to see what is involved in putting a lot of values into one cell...and what is worse is trying to extract it, so PLEASE think about this style of returned info, before you decide it is what you NEED....

Hope this helps

Okay I think I've got it worked out..my VBA is not the greatest, so it took a lot of searching to get the proper format for the second bit of code below
In the example I've added 2 UDFS, and a custom sub
(Note- I've placed all 3 in the same module)
the First UDF:

Please Login or Register  to view this content.

As you can see, I "borrowed" some code that Andrew-R made from here :http://www.excelforum.com/excel-form...59#post3097959 (post #3)
This handles the concatenation of all the responsibilities, using the 'refers to' part of the named range specified by the drop down

the second UDF & the sub: (the sub calls this UDF, so I have grouped them together here)

Please Login or Register  to view this content.

I then Inserted a Command button and in the Assign Macro I used CreateNRs
This creates the list of named ranges to use
(the red D4:D301 is what needs to be changed if you wish to change the size of your jobs list beyond what I have allowed for in the Attachment)

(for use of the code outside the sample)
To use these, Macros must be enabled, then
Hit Alt-F11 to get to VBA editor, Insert-->Module, paste the code into the module, save and close editor

On 'preapproved' sheet
In C4:

Formula:

Please Login or Register  to view this content.

In D4:

Formula:

Please Login or Register  to view this content.

Drag both down to Row 301 (or where you decide to end input)
In E4:

Formula:

Please Login or Register  to view this content.

drag down as far as needed (enough to cover all the Roles expected at least )

(again, red 301's need to be changed if you want to extend the list past there)
Need 1 defined Name Range : Roles refers to: =OFFSET(preapproved!$E$4,,,SUMPRODUCT(--(preapproved!$E$4:$E$301<>"")))
the red 301 here just needs to be large enough to cover the range of the formula in Column E

On 'People' Sheet
In G4:

Formula:

Please Login or Register  to view this content.

drag down to bottom of table (G44)

This should automate the process pretty well for you, you just need to enter the data in the A & B columns of 'preapproved' and hit the Create Lists Button

For the keeping people from editing column G, I selected the entire sheet, Format Cells, Protection...unchecked the Locked option
Then I selected the cells from G4-G44, Format Cells, Protection...re-checked the locked option, Locked the sheet and allowed following allow options, select locked cells (so that you can see the formula),select unlocked cells,use auto-filter (so your filters would still work )
I did something similar on the 'preapproved' sheet to keep the formulas in columns C,D & E from being changed or broken
The sheets are not password protected, so you can un-protect them simply by hittng Un-Protect Sheet on the review tab

See attached

Hope this helps

I guess I may have exceeded the post size limit on last post...It would not let me add the following

the 3 sources I used to come up with CreateNRs() :
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Glad I could help

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"