Search Box

[bugged26]

I have a database with 4 columns. Their names are: Week Ending/ Staff Name/Team/Work TypeOn a seperate tab, I need to create a drop down box so when you select a staff member it brings through all of their entries. However, I would like the data to be presented in a way that would count for each week ending, how many entries a staff memeber made under each team and work type. There are 3 teams and 2 work types, so I would need 6 columns for this.Fo example, the columns would read:Week Ending/ Team 1 Work Type 1 = count/Team 1 Work Type 2 = count and so on.I think it would be useful at the end of each line to have a count function so I know how many cases were done each week in total and at the foot of the table an overall count.Can someone help me with this?

 

[energman58]

Not sure what you want to do with the week number but if on your "results sheet" you have fields for the name the Team and teh Work Type a formula which looks like this

=sumproduct(--(Data!A2:A100=Week),--(Data!B2:B100=Name),--(Data!C2:100=Team),--(D2:D100=WorkType))

Will return the total for the count of matches for the combination of staff member team and work type for each week

 

[bugged26]

Hi,Thanks for this, however, this doesnt cover what I need. Because there are a lot of staff I need a search box so when I select their name from the list it brings through the data, but in the format which I outlined at the start of the exchange.The week ending just represents the week the data was entered onto the spreadsheet. So if this was to work, for each staff memeber there would be 52 lines and then the sum of their work going across the row.

 

[energman58]

So if you have a drop down box in the results sheet for the staff name in cell A1

Set up the rest of your results sheet like this:

in A2 and below you have the week ending dates - it doesnt matter if these are in order or not but I imagine it is best if they are. You can do this with a formula - just put in the most recent week ending date in A2 and in A3 type the following formula

=A2-7

Copy the contents of A3 down as far as you need

This will display the week dates most recent first

in B1 you have the name for Team A
in C1 you have the name for work type 1
in D1 you have the name for work type 2
in E 1 you have the name forwork type3
in F1 you have the name for Team B
in G1 you have the name for work type 1
in H1 you have the name for work type 2
in I1 you have the name forwork type3

In each case it is important that the cell contents are exactly the same as teh names used in your data base sheet (maybe consider using data validation to enforce this on your data sheet)

Assuming the data is in A2:D100 in a sheet called Data

Your formula in C2 should be

=sumproduct(--(Data!$A$2:$A$100=$A2),--(Data!$B$2:$B$100=$A$1),--(Data!$C$2:$C$100=$B$1),--(Data!$D$2:$D$100=C$1))

You can copy that across from C2 to D2

Your Formula in G2 should be

=sumproduct(--(Data!$A$2:$A$100=$A2),--(Data!$B$2:$B$100=$A$1),--(Data!$C$2:$C$100=$F$1),--(Data!$D$2:$D$100=G$1))

You can copy that across from G2 to I2

Then copy C2:I2 down as far as you need to match all the weeks in column A

Each formula then displays the count of that work type for that team for the week in that row for the staff member who appears in cell A1