up vote 2 down vote favorite
share [g+] share [fb]

Google Spreadsheets has a syntax permitting a user to specify an entire row, column, or area with a formula in a single cell. For example, entering ...

=ARRAYFORMULA(ROW(A3:A6))

... into cell C1 creates ...

   C
1  3
2  4
3  5
4  6

The contents of cells C2 through C4 are the formula "CONTINUE."

In Excel, entering {=ROW(A3:A6)} [CTRL] [SHIFT] [ENTER] creates ...

   C
1  3
2  
3  
4

Is there a way to get Excel to populate the remaining rows?

The desired end result is a formula which given ...

   A
1  Fred
2  Wilma
3  Barney
4  Betty

... will generate ...

   B
1  Fred Wilma
2  Barney Betty
3  
4

... without the user having to copy-paste.

link|improve this question
75% accept rate
Did you use shift-enter when entering the formula in excel? – Lance Roberts Nov 1 '10 at 22:39
@Lance Roberts, I used ctrl-shift-enter after entering the formula. Does shift-enter do something different? – Thomas L Holaday Nov 2 '10 at 5:19
nah, I think I just forgot the ctrl. – Lance Roberts Nov 2 '10 at 5:48
feedback

1 Answer

up vote 3 down vote accepted

All you have to do is exactly what you did, except before you enter the formula in excel, select the entire range, A1 through A4. Then type the formula in and hit [ctrl][shift][enter].

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.