Extracting conditional data from different sheet

[ravan16]

Hi,


I'm not sure which sub-forum to post my queries, so I'm posting at 2 sub-forums, Kindly guide if possible.

I need help with the following.
I have 2 worksheets, Preapproved and People
In Preapproved, there is table where each Role being defined with the relevant Responsibility.

What I am trying to achieve here is in the second sheet - People.
1.) Create a dropdown box for Role based from Preapproved sheets Role
2.) Automate the population of the Responsibility in People sheet once the user have selected the right Role.


Together I'm attaching the sample excel sheet > MFG NewHire IT Doc.xlsx

Thanks in Advance.
Let me know if I have posted in the wrong sub-forum

[dredwolf]

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

[ravan16]

Hi Dredwolf,

Thanks, indeed this helps a lot in automating certain process and eliminating human error. Couple of things that might help make this sheet perfect.

1.) Moving forward how do I add more Role & Responsibility to the existing "Preparroved" sheet. Where and what should I modify.
2.) In "People" sheet, I would like to lock column "Responsibility" and make it non editable. As its being auto populated based on "Role"


Based on the formula, I'm assuming it can only display max of 20 responsibilty and as you said its preety long formula. I would be facing difficulities maintaining it in a long term. Based on your expertise what would be the best approach to achieve the desired result other than sol1.xlsx

Thanks again for all your effort and time


Thanks
Ravan

[dredwolf]

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:

Function RangeCon(rngTarget As Range, Optional sDelimiter As String = "", Optional bSkipBlanks As Boolean = True) As String
'written  by Adrew-R  excel forums
'http://www.excelforum.com/excel-formulas-and-functions/894227-extensive-concatenation-issue.html?p=3097959#post3097959
Dim sTmp As String
Dim rngLoop As Range
For Each rngLoop In rngTarget.Cells
  If rngLoop.Value <> "" Or Not (bSkipBlanks) Then
    sTmp = sTmp & rngLoop.Value & sDelimiter
  End If
Next rngLoop
If Len(sTmp) > 0 Then
  sTmp = Left(sTmp, Len(sTmp) - Len(sDelimiter))
End If
RangeCon = sTmp
End Function

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)

'*** Code for Create Named Ranges sub ***

'Check to see if a name exists
Function RNameExists(RName As String) As Boolean
   On Error Resume Next
   RNameExists = Len(ThisWorkbook.Names(RName).Name) <> 0
End Function

'create named ranges
Sub CreateNRs()
Dim l As Range
Dim dRng As Range
Set dRng = Range("D4:D301") 'Change the "D4:D301" to appropriate range
For Each l In dRng
    If l.Value <> "" Then
        If RNameExists(l.Value) Then ThisWorkbook.Names(l.Value).Delete 'if the name alrady exists, delete it (this is to insure proper referencing in the refers to part)
        ThisWorkbook.Names.Add Name:=l.Value, RefersTo:=l.Offset(0, -1).Value, Visible:=True 'create the named range
    End If
Next l
End Sub

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:

=IF(A4="","","="&ADDRESS(ROW(A4),COLUMN(B4),TRUE)&":"&ADDRESS(ROW(A4)+IFERROR(SUMPRODUCT(LARGE(($A5:$A$301<>"")*ROW($A5:$A$301),COUNTA($A5:$A$301)))-ROW(A4)-1,SUMPRODUCT(MAX((B5:$B$301<>"")*ROW(B5:$B$301)))-ROW()),COLUMN(B4))) 

In D4:

=IF(A4="","",SUBSTITUTE(A4," ","_"))

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

=IFERROR(INDEX($A$4:$A$301,SUMPRODUCT(LARGE(($A$4:$A$301<>"")*(ROW($A$4:$A$301)-ROW($A$4)+1),COUNTA($A$4:$A$301)-(ROWS($E$4:E4)-1)))),"")

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:

=IF(F4="","",RangeCon(INDIRECT(SUBSTITUTE(F4," ","_")),"; "))

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

[dredwolf]

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

[ravan16]

Hi Dredwolf,

Appreciate your time spent on this.
Sol2 works perfectly for me.
I am using this sheets extensively in my dailly routines.
Many thanks again.

[dredwolf]

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"