"joeu2004" wrote in message
...
"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.
I think the help section intended the example to be more instructional than
practically useful. Let me just finish this part of my question by saying
that the function works perfectly well in Open Office's Calc. See image
he
http://webpages.charter.net/mrfixite...rayExample.jpg Maybe we
can sidestep this whole discussion and let me explain why I am even
researching this topic. I was interested in converting a Word document which
contained a contact list into Excel format. This list was to be used in a
mail merge to print labels. Long story short, I imported the list into Excel
and that worked fine except the City State and Zip were all included in one
field. I found many ways to break this into separate fields/columns but the
method I liked most required commas between the city and state and the data
document didn't have commas inserted in those positions. So I found this
link with this formula at :
http://www.excelforum.com/showthread.php?t=342639&p=876198&viewfull=1#post87 6198=MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789"))-2-(SEARCH(",",A1)+1))....confirmed with CONTROL+SHIFT+ENTER.I was curious to know how that expression was evaluated and how I couldmodify it to search for any digit, 0-9 within the CityStateZip string. Ithought I would be able to locate where the zip code began in the string andsubsequently I could count spaces backward to find the state and then thecity. This could all be accomplished because there are no cities or stateswhich contain numbers in their names.Thanks for your reply.--David FarberLos Osos, CA