Excel Formula Help

beano4444

Hi, Can anyone tell me if this is possible.

I have a roster with around 30 people on. I have a separate sheet for each month, and a final sheet with everyone's name and then 12 months. At the moment i have to find the name, count how many "H" (H is used for a holiday) then put the amount in the final sheet.

This might not make sense so let me know if you want more info

Thanks

WTFH

So for the January sheet, what do you have?
Is it 31 days across the top, then the names down the left, then H against the days they are on holiday?

beano4444

WTFH wrote: »

So for the January sheet, what do you have?
Is it 31 days across the top, then the names down the left, then H against the days they are on holiday?

That's correct, it has D for Days, N for Nights and H for holiday

WTFH

OK, how I would do it (not saying this is best, it also has a few cheats in it)
If their names are in Column A, then instead of having the month start in B1, move it to C1.
If the first name is in A2, then in B2 put the following:
=COUNTIF(C2: AG2,"H")
(Note, take the space out between the C2: and the AG2 - this forum converts : A to :A
Then copy that formula down for all the names. Hey presto, column B contains the total number of holidays per person in that month.

bod1467

Let's assume on the final worksheet (HOLIDAYS) column A is the names, Column B is January, column C is February etc. The first worksheet is called JANUARY.

HOLIDAYS:Cell A2 is Bert

HOLIDAYS:Cell B2 refers to holidays in January for Bert. The formula in B2 is:

JANUARY:Cell A2 is Bert; JANUARY cells B2 to AF2 are days 1 to 31 in January. An H is placed against each relevant cell/date

=COUNTIF(JANUARY!B2:AF2,"H") is the formula in cell B2 in HOLIDAYS

That gives the number of cells in worksheet JANUARY, cells B2 to AF2 (equating to 1 to 31 days) that have H in them.

That should give you the idea for the rest.

WTFH

Second part of my earlier post:

Let's assume you've called each sheet Jan, Feb, Mar, etc.
In the final sheet, list the names out again.
Now, my CHEAT is that the names must always be in the same order in every sheet to make the code simple. If a name is added, it must go in every sheet in the same place. If one is taken away, it must be removed from every sheet.
(I'll work on a technically better solution later)

If the names are in column A, then in B2 put:
=Jan!B2+Feb!B2+Mar!B2 (etc)
That will then add up the totals from each sheet

beano4444

bod1467 wrote: »

Let's assume on the final worksheet (HOLIDAYS) column A is the names, Column B is January, column C is February etc. The first worksheet is called JANUARY.

HOLIDAYS:Cell A2 is Bert

HOLIDAYS:Cell B2 refers to holidays in January for Bert. The formula in B2 is:

JANUARY:Cell A2 is Bert; JANUARY cells B2 to AF2 are days 1 to 31 in January. An H is placed against each relevant cell/date

=COUNTIF(JANUARY!B2:AF2,"H") is the formula in cell B2 in HOLIDAYS

That gives the number of cells in worksheet JANUARY, cells B2 to AF2 (equating to 1 to 31 days) that have H in them.

That should give you the idea for the rest.

Thanks for the info. The only thing is that with people leaving, or joining plus additional ad hoc contractors being added to the roster, the name may now always be in the same cell, eg Bert might be in A2 in Jan but in Feb he could be in A14

bod1467

Then you're using the wrong medium. What you're after is better suited to a database with reports.

WTFH

OK, the technically better (no cheats) version of the results spreadsheet.
List the names starting at A2. Put this in B2:

=  (VLOOKUP(A2,Jan!$A$2:$B$35,2,FALSE) 
+VLOOKUP(A2,Feb!$A$2:$B$35,2,FALSE)
 +VLOOKUP(A2,Mar!$A$2:$B$35,2,FALSE)
 +VLOOKUP(A2,Apr!$A$2:$B$35,2,FALSE)
 +VLOOKUP(A2,May!$A$2:$B$35,2,FALSE)
 +VLOOKUP(A2,Jun!$A$2:$B$35,2,FALSE)
+VLOOKUP(A2,Jul!$A$2:$B$35,2,FALSE)
 +VLOOKUP(A2,Aug!$A$2:$B$35,2,FALSE)
  +VLOOKUP(A2,Sep!$A$2:$B$35,2,FALSE)
 +VLOOKUP(A2,Oct!$A$2:$B$35,2,FALSE)
  +VLOOKUP(A2,Nov!$A$2:$B$35,2,FALSE) 
+VLOOKUP(A2,Dec!$A$2:$B$35,2,FALSE))

Then copy it down to B3... all the way to the last name. I've set that code to work for up to 33 names. If you have more names than that, change $B$35 (33 names) to $B$38 (36 names) , or however many it need to be.

This allows for people joining/leaving, and it doesn't matter what order they are in for each month.
You still need to do the bit as I described in post 4 - i.e. add a new column for each month

bod1467

The only thing is ... all the names must be unique.

WTFH

bod1467 wrote: »

The only thing is ... all the names must be unique.

Or give them all employee numbers, if that's going to be an issue