Doug Mutzig
Board Regular
- Joined
- Jan 1, 2019
- Messages
- 57
- Office Version
- 365
- Platform
- Windows
Good afternoon all,
First off let me apologize for the title, I tried to describe what is needed but it still sounds confusing.
Background information:
I have 3 tables:
1. Shift code legend (contains 3 columns - Code, Description, FTE value). table is named: T_ShiftCode
2. Hours Calculating Reference (Contains 13 columns - for this issue only 2 are neede: LPN Codes and LPN Value. Table is named: T_HOURS
3. On a different tab I have another table containing multiple columns including Staff Name, Service, Skill, Day1, Day2, etc. This table is a schedule for 4 weeks
They way things currently work is the end user can add/remove Shift codes so that they can tailor for their department/unit. They then give each code an FTE value (4hrs= .5 FTE value, 8hrs= 1 FTE value, and 12hrs = 1.5 FTE value)
Next the end user will enter the codes they want associated with a particular Skill (LPN, RN, etc.) in the HOURS Table. When they enter in the code there is a second column that auto-populated the FTE value from the Shift code table.
Finally on the Schedule the end user will enter a code for each staff member for each day.
For example I have 5 staff members (Andy, Betty, Carl, Dan, Ed). I have 6 shift codes (1,2,3,4,CH,NM) with different FTE values (1 through 4 are .5, CH = 1, and NM=1.5) which I enter on the SHift Code legend. Andy, Betty, Ed are LPNs and on the schedule for Day 1 I enter the code 2 for Andy, CH for Betty, and 3 for Ed.
What I am trying to do is get an accurate count of FTEs for the day (i.e. Andy = 2 = .5 + Betty = CH = 1, Ed = 3 = .5 for a total FTE count of 2) .
Thinking through the logic I have:
1. For Day 1 find all of the LPNs (in the example 3 LPNs for day 1)
2. Sum the number of LPNs for each code ( code 2 = 1 LPN, code CH = 1 LPN, code 3 = 1 LPN)
3. For each code get the FTE value
4. Multiply each sum of LPNs per code by code's FTE value ( 1 * .5 (code 2) + 1*1 (code CH) + 1*.5 (code 3) = 2)
but for the life of me I cannot seem to get anything to work. I have tried vlookups, Index/matches, sumproduct and countifs and just cant get anything other than an error.
I can get an accurate count of people using:
I can get the FTE value using:
But trying to join them to do a sum for the entire day does not work
I have tried:
receiving a #Value ! error. It seems that the R_LPNs (range of the LPN column in the HOURS table) does not resolve correctly when I evaluate the formula. I have also tried T_FTE[LPN Codes] with the same result.
Does anyone have an advice on how to tackle this? I am trying to not hardcode the shiftcodes into the formulas as things change often and I want to make it so that the end user can update a table entry or two and still have the calculations work.
Please let me know if I have forgotten to provide some information, and THANK YOU for any and all help you can provide.
Doug
First off let me apologize for the title, I tried to describe what is needed but it still sounds confusing.
Background information:
I have 3 tables:
1. Shift code legend (contains 3 columns - Code, Description, FTE value). table is named: T_ShiftCode
2. Hours Calculating Reference (Contains 13 columns - for this issue only 2 are neede: LPN Codes and LPN Value. Table is named: T_HOURS
3. On a different tab I have another table containing multiple columns including Staff Name, Service, Skill, Day1, Day2, etc. This table is a schedule for 4 weeks
They way things currently work is the end user can add/remove Shift codes so that they can tailor for their department/unit. They then give each code an FTE value (4hrs= .5 FTE value, 8hrs= 1 FTE value, and 12hrs = 1.5 FTE value)
Next the end user will enter the codes they want associated with a particular Skill (LPN, RN, etc.) in the HOURS Table. When they enter in the code there is a second column that auto-populated the FTE value from the Shift code table.
Finally on the Schedule the end user will enter a code for each staff member for each day.
For example I have 5 staff members (Andy, Betty, Carl, Dan, Ed). I have 6 shift codes (1,2,3,4,CH,NM) with different FTE values (1 through 4 are .5, CH = 1, and NM=1.5) which I enter on the SHift Code legend. Andy, Betty, Ed are LPNs and on the schedule for Day 1 I enter the code 2 for Andy, CH for Betty, and 3 for Ed.
What I am trying to do is get an accurate count of FTEs for the day (i.e. Andy = 2 = .5 + Betty = CH = 1, Ed = 3 = .5 for a total FTE count of 2) .
Thinking through the logic I have:
1. For Day 1 find all of the LPNs (in the example 3 LPNs for day 1)
2. Sum the number of LPNs for each code ( code 2 = 1 LPN, code CH = 1 LPN, code 3 = 1 LPN)
3. For each code get the FTE value
4. Multiply each sum of LPNs per code by code's FTE value ( 1 * .5 (code 2) + 1*1 (code CH) + 1*.5 (code 3) = 2)
but for the life of me I cannot seem to get anything to work. I have tried vlookups, Index/matches, sumproduct and countifs and just cant get anything other than an error.
I can get an accurate count of people using:
Code:
=SUMPRODUCT(COUNTIFS(T_P1Dtest[D1],R_LPNs,T_P1Dtest[Skill],"LPN"))
Code:
=VLOOKUP(T_P1Dtest[D1],T_HOURS,3,FALSE)But trying to join them to do a sum for the entire day does not work
I have tried:
Code:
=SUMPRODUCT(COUNTIFS(T_P1Dtest[D1],R_LPNs,T_P1Dtest[Skill],"LPN")*INDEX(T_HOURS,MATCH(R_LPNs,R_T_DUTY,0),3))receiving a #Value ! error. It seems that the R_LPNs (range of the LPN column in the HOURS table) does not resolve correctly when I evaluate the formula. I have also tried T_FTE[LPN Codes] with the same result.
Does anyone have an advice on how to tackle this? I am trying to not hardcode the shiftcodes into the formulas as things change often and I want to make it so that the end user can update a table entry or two and still have the calculations work.
Please let me know if I have forgotten to provide some information, and THANK YOU for any and all help you can provide.
Doug
