On 2002-03-15 06:17, rcranley wrote:
I have an Excel sheet with a large list of names. Some of the names are repeated as the list shows members of different user groups in my organisation, and of course a person can be a member of more than 1 user group.
I want to build a small search facility at the top of the sheet, in which when a name is selected from a drop down list, it shows how many lists they are in and a dynamically created drop down list with the names of the groups.
Example:-
Intranet Users Internet Users Admin Users
Joe Bloggs Joe bloggs Joe Bloggs
Mary Smith John Doe John Doe
John Doe Jim Beam
Jim Beam
Etc.
I have the 'how many' part working but can't get the new drop down list to work.
e.g.
Running the query on 'Mary Smith' would result in 1 and on 'Jim Beam' would result in 2 etc.
But I also want a drop down list that would contain 'Intranet Users' only for Mary Smith but 'Intranet Users, Internet Users, Admin Users' for John Doe.
Can you help me, please??
Thanking you in advance
Make a 1-column list of registered users in a worksheet named
Admin from A1 on:
{"Registered Users";"Joe Bloggs";"Mary Smith";"John Doe";"Jim Beam";"aky"}
Activate A2.
Activate Insert|Name|Define.
Enter USERS as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET(Admin!$A$2,0,0,COUNTA(Admin!$A:$A)-1,1)
Click OK.
I'll asume that the relevant data is in a worksheet named
Data.
Lets say that A1:C5 in Data houses the sample data you provided:
{"Intranet Users","Internet Users","Admin Users";
"Joe Bloggs","Joe bloggs","Joe Bloggs";
"Mary Smith","John Doe","John Doe";
"John Doe","Jim Beam","";
"Jim Beam","",""}
Note 1. "" stand for an empty cell.
In E1 enter: User Groups [ just a label ]
In E2 enter: =IF(ISNUMBER(MATCH(Query!$A$3,$A$2:$A$5,0)),$A$1,"")
In E3 enter: =IF(ISNUMBER(MATCH(Query!$A$3,$B$2:$B$5,0)),$B$1,"")
In E4 enter: =IF(ISNUMBER(MATCH(Query!$A$3,$C$2:$C$5,0)),$C$1,"")
Note 2. The formulas from E2 on are checking one by one whether a given user is a member in a given group. Thus, a separate formula per group given the layout of your data.
In column F from F2 on enter the following sets of labels:
{"Groups";"LocList";"SortList";"NoDupsList"}
In G1 enter:
=COUNTA($E$1:$E$10)-1
Note 3. Adjust the range $E$1:$E$10 to suit the number of user groups that you have.
In G2 enter:
="$E$2:"&"$E"&$G$1+1
In G3 enter:
="$H$2:"&"$H$"&COUNTA(H:H)+1
In G4 enter:
="$I$2:"&"$I$"&COUNTA(I:I)+1
In G5 enter:
="$J$2:"&"$J$"&COUNTA(I:I)+1
In H2 enter and copy down till 10th row:
=IF(LEN(E2),SUMPRODUCT((E2>INDIRECT($G$2))+0)+1,"")
Note 4. The number 10 is the assumed number of user groups, which you must adjust to suit.
In I2 enter:
=IF(LEN(H2)=0,"None",IF(ISNUMBER(MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),INDEX(INDIRECT($G$2),MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),0))
In I3 enter and copy down till the 10th row:
=IF(ISNUMBER(MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),INDEX(INDIRECT($G$2),MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),0)
In J2 array-enter and copy down till the 10th row:
=IF(ROW()-ROW(INDIRECT($G$5))+1>ROWS(INDIRECT($G$4))-COUNTIF(INDIRECT($G$4),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($G$4)<>0,ROW(INDIRECT($G$4)),ROW()+ROWS(INDIRECT($G$4)))),ROW()-ROW(INDIRECT($G$5))+1),COLUMN(INDIRECT($G$4)))))
Note 5. You need to hit control+shift+enter at the same time, not just enter, to array-enter a formula.
Activate J2.
Activate Insert|Name|Define.
Enter GROUPS as name in the Names in the Workbook box.
Enter as formula in the Refers to box:
=OFFSET(Data!$J$2,0,0,COUNTIF(Data!$J$2:$J$10,"*?*"),1)
Click OK.
Now we are in the main worksheet to which I will refer as
Query.
In Query:
In A2:C2 enter the following labels:
{"User","# Groups","Groups"}
Activate A3.
Activate the option Data|Validation.
Choose 'List' for Allow.
Enter as 'Source':
=USERS
Click OK.
In B3 enter:
=COUNTIF(Data!A2:C5,A3)
Activate C3.
Activate the option Data|Validation.
Choose 'List' for Allow.
Enter as 'Source':
=GROUPS
Click OK.
Aladin
PS. If interested in a WB that uses the above system of formulas, just drop me a line.
