Identify a cell by letter but count the number alongside the letter

P

Pablo78

New Member
Joined
Oct 9, 2017
Messages
13
Hi all,

I am putting together a Staff planner for my business, I have just under 30 employees.

Each employee has a total 200 hours paid vacation per year. What I am trying to do is make a sort of calendar which is marked with the amount of hours they are scheduled to work each day. See my example below.

I have made some conditional formatting rules to format certain letters if they're entered, these letters represent a reason for absence.

I currently have the letter H set for vacations, which formats the cell green and the text Bold White. I would like to make a formula that counts the 3rd digit of each cell that has a letter H present. So basically my cell would say H-4 meaning the employee had taken 4 hours vacation that day. In another cell I will have their Vacation hours (200) - the days booked, days booked in the planner will say H-8, etc. Once their hours are used the column will show a 0.

I think this should be possible, tried a few formulas but just can't seem to get it right.

Employee 1 for example would count at 24 or 200 - 24 = 176
Employee 2 for example would count at 24 or 200 - 20 = 180

Hope this makes sense.

2021 Staffing Plan.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1SummaryApproved Vacation to DateJanuary 2021 Q1
2SSICKMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
3CCOVID-19
4VVAB
5AATF
6PPARENTAL LEAVE28-Dec-2029-Dec-2030-Dec-2031-Dec-2001-Jan-2102-Jan-2103-Jan-2104-Jan-2105-Jan-2106-Jan-2107-Jan-2108-Jan-2109-Jan-2110-Jan-2111-Jan-2112-Jan-2113-Jan-2114-Jan-2115-Jan-2116-Jan-2117-Jan-2118-Jan-2119-Jan-2120-Jan-2121-Jan-2122-Jan-2123-Jan-2124-Jan-2125-Jan-2126-Jan-2127-Jan-2128-Jan-2129-Jan-2130-Jan-2131-Jan-21
7KKOMP
8UUNPAID
9RREMOTE
10HVACATION0000000000000000000000
1101Site ManagerKenneth BackgårdH-8H-8H-88888888888888888888
1202Mobilisation PMPaul DoyleH-8H-488888888888888888888
Summary
Cell Formulas
RangeFormula
E10:G10,AG10:AK10,Z10:AD10,S10:W10,O10:P10,L10:M10E10=SUM(E33:E112)/8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AL11:AM32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
AE11:AF32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
X11:Y32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
Q11:R32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
N11:N32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
H11:K32Cell Valuecontains ""textNO
NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32,JY19:KC32Cell Value<1textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
AE10:AF10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
X10:Y10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
Q10:R10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
N10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
H10:K10Cell Valuecontains ""textNO
NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32,JY19:KC32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
E113:OM113,NS19:NW32,DM19:DQ32,DT19:DX32,EA19:EE32,EO19:ES32,EV19:EZ32,FC19:FG32,FJ19:FN32,FQ19:FU32,FX19:GB32,GE19:GI32,GL19:GP32,GS19:GW32,GZ19:HD32,HG19:HK32,HN19:HR32,HU19:HY32,IB19:IF32,II19:IM32,IP19:IT32,IW19:JA32,JD19:JH32,JK19:JO32,JR19:JV32Cell Valuecontains ""textNO
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
I know the conditional formatting is a mess and there are other formulas that need to be tidied up but I just need to be able to add the third letter of each cell together if possible.

I can fix everything else later, thanks.
 
Upvote 0
I'm not sure where you want the formulas, but consider:

Book1
CDEFGHIJKLM
10AllH only
113224H-8H-8H-8S-888
122716H-8H-88P-4S-7
Sheet2
Cell Formulas
RangeFormula
C11:C12C11=SUMPRODUCT(("0"&MID(E11:Z11,3,1))+0)
D11:D12D11=SUMPRODUCT(--(LEFT(E11:Z11)="H"),("0"&MID(E11:Z11,3,1))+0)


If you want to find the remaining hours, use:

Excel Formula: 
=200-SUMPRODUCT(("0"&MID(E11:Z11,3,1))+0)
 
Upvote 0
Solution
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top