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
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