"David Farber" wrote:
The Excel help says that if you put, ROW() into a cell,
I used C4, then the row number should appear and that
does work. In the second example it says that if you put
ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows
below the formula should populate with 4, 5, 6. What
happens when I try this is that I only get a 4 displayed
in the cell, C4, where the formula is. The other cells
below are left blank.

You probably overlooked the subtle instructions. According to the Help
page, after you normally-enter (just press Enter as usual) the formula into
A2, the instructions ask you to select A2:A4, press F2, then press
ctrl+shift+Enter to array-enter the formula.

But please note: that method is nonsensical.

Sometimes the help pages show you how to do things in order to demonstrate
form or method. That does not mean it is the best way to accomplish the
task.

First, it is nonsensical to use ROW(C4:D6) in that context. Since we are
entering the formula into a single column, the ROW parameter should be a
single column, e.g. ROW(C4:C6).

Second, it is nonsensical to refer to another column unnecessarily, C4:C6 in
this case. And that goes double for original range C4:D6.

The problem that causes is: it creates a dependency on those cells. So
whenever any of C4:C6 is modified, A2:A4 will be recalculated.

Sometimes that is our intent. And even if it isn't, it is not a big deal in
this very simple case.

But generally, it could be a big deal when ROW(C4:C6) is part of a more
complicated and time-consuming formula, e.g. a lookup operation.

So ROW(A4:A6) would be a better choice in this context.

Finally, it is nonsensical to use an array-entered formula at all in this
particular example. Simply normally-enter =ROW(A4) into A2, then copy the
formula down through A6.

I avoid array-entered formulas, especially multi-cell array-entered
formulas. They are difficult to modify.

For example, if you had array-entered =ROW(A4:A6) into A2:A4, then tried to
replace the formula in A2 with =ROW(A4) as I suggested, you probably
encountered an error to the effect "cannot change part of an array".

To avoid the error, we must first select A2:A4 and delete the formula.