I have this formula at cell E2:
This is to find the instance/occurrence of the value at D2 counting from above, but not below it.
Logically to me, when Excel autofills, the formula at E3 shoud be:
[QUOTE}=RIGHT($D2&"-"&COUNTIF($D$2:$D3,$D3),LEN($D2&"-"&COUNTIF($D$2:$D3,$D3))-FIND("-",$D3&"-"&COUNTIF($D$2:$D3,$D3),FIND("-",$D2&"-"&COUNTIF($D$2:$D3,$D3))+1))[/QUOTE]
But instead, when autofilling, the first formula changes to this:
The fourth formula becomes this:
And even worse, the fifth formula becomes this:
Why is this happening? Why is the array not expanding step-by-step? And why would the first formula change on its own? The data comes with headers and are formatted as a table.
=RIGHT($D2&"-"&COUNTIF($D$2:$D2,$D2),LEN($D2&"-"&COUNTIF($D$2:$D2,$D2))-FIND("-",$D2&"-"&COUNTIF($D$2:$D2,$D2),FIND("-",$D2&"-"&COUNTIF($D$2:$D2,$D2))+1))
This is to find the instance/occurrence of the value at D2 counting from above, but not below it.
Logically to me, when Excel autofills, the formula at E3 shoud be:
[QUOTE}=RIGHT($D2&"-"&COUNTIF($D$2:$D3,$D3),LEN($D2&"-"&COUNTIF($D$2:$D3,$D3))-FIND("-",$D3&"-"&COUNTIF($D$2:$D3,$D3),FIND("-",$D2&"-"&COUNTIF($D$2:$D3,$D3))+1))[/QUOTE]
But instead, when autofilling, the first formula changes to this:
=RIGHT($D2&"-"&COUNTIF($D$2:$D8,$D2),LEN($D2&"-"&COUNTIF($D$2:$D8,$D2))-FIND("-",$D2&"-"&COUNTIF($D$2:$D8,$D2),FIND("-",$D2&"-"&COUNTIF($D$2:$D8,$D2))+1))
The fourth formula becomes this:
=RIGHT($D5&"-"&COUNTIF($D$2:$D8,$D5),LEN($D5&"-"&COUNTIF($D$2:$D8,$D5))-FIND("-",$D5&"-"&COUNTIF($D$2:$D8,$D5),FIND("-",$D5&"-"&COUNTIF($D$2:$D8,$D5))+1))
And even worse, the fifth formula becomes this:
=RIGHT($D6&"-"&COUNTIF($D$2:$D11,$D6),LEN($D6&"-"&COUNTIF($D$2:$D11,$D6))-FIND("-",$D6&"-"&COUNTIF($D$2:$D11,$D6),FIND("-",$D6&"-"&COUNTIF($D$2:$D11,$D6))+1))
Why is this happening? Why is the array not expanding step-by-step? And why would the first formula change on its own? The data comes with headers and are formatted as a table.