Here's how I approach this...

I use local scope (sheet level) defined names for ranges rather
extensively (as a 'best practice') so my formulas (or code) is better
'self-documented' and so easier to understand. Local scope also allows
me to reuse the same name[s] on other sheets in the same workbook
without conflict.

Define local scope names as follows...

'Sheet Name'!DefinedName

...where the apostrophes wrap the sheet name when it contains spaces.
Otherwise, the apostrophes aren't required. Note that the exclamation
(!) character is used to delimit the string.

Make sure *A2* is selected and create the following names in the Define
Name dialog. (Assumes sheetname is "Orders"

Name1: orders!LastCell
RefersTo: =A1

Name2: orders!ThisOrderNum
RefersTo: =$B2

Name3: orders!AboveOrderNum
RefersTo: =$B1

Name4: orders!OrderCount
RefersTo: =IF(ThisOrderNum<AboveOrderNum,LastCell+1,LastCel l)

Note that while this name method works in all versions of Excel, the
later versions allow you to select the sheetname from the 'Scope:'
dropdown. (I typically copy everything from the exclamation and left to
save extra steps when entering multiple names)

How this works:
Requires A1 is *blank* because A2 needs to start at 1.
If your column *requires* a header then insert a blank row below
the headers and hide it

-OR-
manually enter 1 to start the counter in A2.

All 3 cell names are row-relative refs to the cell containing the
formula using those names;

Names 2&3 are column-absolute so your counter can be used in any
column you wish;

Enter the following formula in A2 and copy down...
=OrderCount
..or select all cells to receive the formula, enter it once, then
use Ctrl+Enter to populate the selection.

This method will auto-adjust if rows are inserted/deleted.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus