Find the MAX range in a row from the last "\" for a Duty Roster

[ibredhd]

I want to create a formula that will grow when dragged to the right and down and be able to respond when text is wrote over it. Ive got it to work but when I enter an "A" after a "\" has been entered previously in the row it finds the max of the whole row. I need it to find the max since the last "\" up to itself.

Row 1 contains sequencial dates for the next 365 days
Column A can contain any number up to 365, "A", or "\" and serves as the start point for the formula

Cell B2 contains the below formula and is drag to the right and then down

=IF(ISNUMBER(A2),A2+1,((IF(A2="\",1,IF(A2="A",MAX($A2:A2)+1,"E")))))

The following are the rules for a Duty Roster
"\" signifies duty and the count restarts to 1
"A" signifies leave and should not count forward

My plan is to manually enter the "\" for the person with the maximum number in each column, however, I am open to other ways.

There will be at least 150 people tracked on this roster

Any help would be appriciated

 

[sijpie]

Excel 2010
	A	B	C	D	E	F	G
1
2	3	4	\	1	2	3	4
3	5	6	7	8	\	1	2
4	2	3	4	\	1	2	3
5	\	1	2	A	3	4	5
6	6	7	8	9	10	\	1
7	\	1	2	3	4	5	A
Sheet1

Cell Formulas
Range	Formula
C3	=IF(ISNUMBER(B3),B3+1,((IF(B3="\",1,IF(B3="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A3:B3="\")*COLUMN($A3:B3))),0)+65)&ROW(B3)&":"&CELL("address",B3)))+1,"E")))))
C4	=IF(ISNUMBER(B4),B4+1,((IF(B4="\",1,IF(B4="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A4:B4="\")*COLUMN($A4:B4))),0)+65)&ROW(B4)&":"&CELL("address",B4)))+1,"E")))))
C6	=IF(ISNUMBER(B6),B6+1,((IF(B6="\",1,IF(B6="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A6:B6="\")*COLUMN($A6:B6))),0)+65)&ROW(B6)&":"&CELL("address",B6)))+1,"E")))))
C7	=IF(ISNUMBER(B7),B7+1,((IF(B7="\",1,IF(B7="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A7:B7="\")*COLUMN($A7:B7))),0)+65)&ROW(B7)&":"&CELL("address",B7)))+1,"E")))))
D3	=IF(ISNUMBER(C3),C3+1,((IF(C3="\",1,IF(C3="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A3:C3="\")*COLUMN($A3:C3))),0)+65)&ROW(C3)&":"&CELL("address",C3)))+1,"E")))))
D2	=IF(ISNUMBER(C2),C2+1,((IF(C2="\",1,IF(C2="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A2:C2="\")*COLUMN($A2:C2))),0)+65)&ROW(C2)&":"&CELL("address",C2)))+1,"E")))))
D6	=IF(ISNUMBER(C6),C6+1,((IF(C6="\",1,IF(C6="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A6:C6="\")*COLUMN($A6:C6))),0)+65)&ROW(C6)&":"&CELL("address",C6)))+1,"E")))))
D7	=IF(ISNUMBER(C7),C7+1,((IF(C7="\",1,IF(C7="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A7:C7="\")*COLUMN($A7:C7))),0)+65)&ROW(C7)&":"&CELL("address",C7)))+1,"E")))))
B2	=IF(ISNUMBER(A2),A2+1,((IF(A2="\",1,IF(A2="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A2:A2="\")*COLUMN($A2:A2))),0)+65)&ROW(A2)&":"&CELL("address",A2)))+1,"E")))))
B3	=IF(ISNUMBER(A3),A3+1,((IF(A3="\",1,IF(A3="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A3:A3="\")*COLUMN($A3:A3))),0)+65)&ROW(A3)&":"&CELL("address",A3)))+1,"E")))))
B4	=IF(ISNUMBER(A4),A4+1,((IF(A4="\",1,IF(A4="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A4:A4="\")*COLUMN($A4:A4))),0)+65)&ROW(A4)&":"&CELL("address",A4)))+1,"E")))))
B6	=IF(ISNUMBER(A6),A6+1,((IF(A6="\",1,IF(A6="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A6:A6="\")*COLUMN($A6:A6))),0)+65)&ROW(A6)&":"&CELL("address",A6)))+1,"E")))))
B7	=IF(ISNUMBER(A7),A7+1,((IF(A7="\",1,IF(A7="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A7:A7="\")*COLUMN($A7:A7))),0)+65)&ROW(A7)&":"&CELL("address",A7)))+1,"E")))))
E4	=IF(ISNUMBER(D4),D4+1,((IF(D4="\",1,IF(D4="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A4:D4="\")*COLUMN($A4:D4))),0)+65)&ROW(D4)&":"&CELL("address",D4)))+1,"E")))))
E2	=IF(ISNUMBER(D2),D2+1,((IF(D2="\",1,IF(D2="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A2:D2="\")*COLUMN($A2:D2))),0)+65)&ROW(D2)&":"&CELL("address",D2)))+1,"E")))))
E6	=IF(ISNUMBER(D6),D6+1,((IF(D6="\",1,IF(D6="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A6:D6="\")*COLUMN($A6:D6))),0)+65)&ROW(D6)&":"&CELL("address",D6)))+1,"E")))))
E7	=IF(ISNUMBER(D7),D7+1,((IF(D7="\",1,IF(D7="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A7:D7="\")*COLUMN($A7:D7))),0)+65)&ROW(D7)&":"&CELL("address",D7)))+1,"E")))))
F4	=IF(ISNUMBER(E4),E4+1,((IF(E4="\",1,IF(E4="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A4:E4="\")*COLUMN($A4:E4))),0)+65)&ROW(E4)&":"&CELL("address",E4)))+1,"E")))))
F2	=IF(ISNUMBER(E2),E2+1,((IF(E2="\",1,IF(E2="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A2:E2="\")*COLUMN($A2:E2))),0)+65)&ROW(E2)&":"&CELL("address",E2)))+1,"E")))))
F7	=IF(ISNUMBER(E7),E7+1,((IF(E7="\",1,IF(E7="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A7:E7="\")*COLUMN($A7:E7))),0)+65)&ROW(E7)&":"&CELL("address",E7)))+1,"E")))))
G2	=IF(ISNUMBER(F2),F2+1,((IF(F2="\",1,IF(F2="A",MAX(INDIRECT(CHAR(IFERROR(SUMPRODUCT(MAX(($A2:F2="\")*COLUMN($A2:F2))),0)+65)&ROW(F2)&":"&CELL("address",F2)))+1,"E")))))

This will work until column Z.

If your data goes further than the formula becomes very long. See here How to get the current column name in Excel? - Super User for some solutions (one would be the lookup of the column letter in a named range)

 

[ibredhd]

Excellent!

Your formula works perfect until column Z. It will work great for this month and Ill set some research time aside to tryp and get it to work for a 365 day calendar in the future.

Thanks for the Help

- Joe