Basically I am using excel to make a work schedule. What I want to do is have it update automatically with how many people I have for day or for night. I have done this using a separate sheet and a lookup table, and a "count if" and just having the value from second sheet update to main schedule. The issues I have are; I need to find a way to have some of the values ( IE 11-10, 8-8 etc ) count for both day and night if possible. I also would really like to find a way to cut out the need for a secondary sheet as I'll run into nightmares if I have to add or delete new rows for additional employees from sheet 1 .
My thoughts were to use a countIF and a vlookup in the same formula, but my google and mrexcel forums searchs for how to do that have proven fruitless. Essentially have the formula check the table to see if 6-2 is day or night and if it is day add 1 to the day cell, have 11-10 add 1 to day cell and 1 to night cell.
I currently have 18 different sets of hours possible so I'm fairly certain I need to use a table. Also, the hours posted in main schedule have to be in text format otherwise they get treated as an equation.
Any advice would be greatly appreciated.