rows function not returning the expected result

I would like to sort this data out initially by column C, the #, and then by the item name.

Ultimately I will then generate a new calculated # which will be the original # if it exists, or a serial number that follows on from the last known #.

In trying to do this, I have come across what appears to be an inconsistency in the rows() function and want to confirm that I am not going daft. I have an alternative method using countif() for generating the calculated #.

Here is the issue:

Column D contains simple row functions, for example D11 contains =ROW($A11). It should and does return 11.

Notice that item VB is in row 14 and the function in D14 returns 14, as expected.

When I sort the data, based on # first, and then item name second, the sort produces the correct results but the row() functions return the incorrect values, the values from before the sort.

item VB has correctly moved to row 11 but the formula in column D is still returning the value 14, i.e. rows() returns the incorrect result.

This behaviour only seems to occur when sorting while categories are enabled.

If categories are turned off and then back on again, the rows functions return the correct values.

Is this behaviour intentional?

[Edited by Moderator]