OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Cannot Fill Down as in Excel!

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
spex074
Newbie
Newbie


Joined: 05 Nov 2008
Posts: 1

PostPosted: Wed Nov 05, 2008 10:20 am    Post subject: Cannot Fill Down as in Excel! Reply with quote

Help...I am a new user to Open Office Calc and am having a tough time with the transition from Excel. The most common key strokes I used in Excel were CTRL + D which would copy the cell above data into the cell below. Open Office Calc does not seem to have this option. If there is a way of adding this as a shortcut/macro, could someone please walk me through this, in simple terms...
Much appreciation!
E
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Wed Nov 05, 2008 11:17 am    Post subject: Reply with quote

You Autofill by Clicking the cell with your data and grabbing the lower right hand corner of the cell and dragging down while holding the Ctrl key down. If you don't want to repeat the same data drag the lower right hand cell corner down to fill with a series.

From the V3.0 help:
You can automatically fill cells with data with the AutoFill command or the Series command.
Using AutoFill
AutoFill automatically generates a data series based on a defined pattern.
1.On a sheet, click in cell, and type a number.
2.Click in another cell and then click back in the cell where you typed the number.
3.Drag the fill handle in the bottom right corner of the cell across the cells that you want to fill, and release the mouse button.
The cells are filled with ascending numbers.

To quickly create a list of consecutive days, enter "Monday" in a cell, and drag the fill handle.

Hold down Ctrl if you do not want to fill the cells with different values.

If you select two or more adjacent cells that contain different numbers, and drag, the remaining cells are filled with the arithmetic pattern that is recognized in the numbers. The AutoFill function also recognizes customized lists that are defined under Tools - Options - OpenOffice.org Calc - Sort Lists.


You can double-click the fill handle to automatically fill all empty columns of the current data block. For example, first enter Jan into A1 and drag the fill handle down to A12 to get the twelve months in the first column. Now enter some values into B1 and C1. Select those two cells, and double-click the fill handle. This fills automatically the data block B1:C12.
Back to top
View user's profile Send private message
Richard1
General User
General User


Joined: 20 Sep 2009
Posts: 8

PostPosted: Fri Sep 25, 2009 6:39 am    Post subject: Reply with quote

So are we saying there is no way to duplicate the Control-D?

Then what is the best way to:

Fill down the formulas from Row 1, columns A through CE, down to row 5000?

Do we have to highlight A1-CE1 and then drag slowly down the page? instead of highlighting row 1, hitting shift, dropping down to row 5000 clicking on the row and Control-D?

Same goes for an individual column. is it then necesary to drag the corner 6000 rows down?
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Sep 25, 2009 7:05 am    Post subject: Reply with quote

Learn to use the usual navigation keys (arrows, Home, End with ctrl and shift). They are the same as in Excel. Named ranges, the navigator and the name box left of the formula bar may also help.
Database ranges for huge lists are the most important feature in this context (not in Excel).

1. Copy first row, select the rest and paste. This will preserve filtered rows.

2. Select the whole thing and Edit>Fill>Down. This action affects all rows
You may install this macro suite and replace the 4 Edit>Fill>down|right|up|left commands. Edit>Fill>... will preserve all hidden rows and columns then.

3. If you want to add one calculated field to a list, put the formula below the header and double-click the tiny cell handle. This drags down until the end of the adjacent column's used range.

Talking about 6000+ rows, allow me to add my usual hint: a spreadsheet is a very insufficient replacement for a database. Excel does not include a database, but OOo does. Base may be considered the worst tool on the market, but it is a far better list keeper than Excel. It serves consistent row sets to all office documents. As a calculation tool, Calc works far better with imported database data.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Sep 25, 2009 7:15 am    Post subject: Re: Cannot Fill Down as in Excel! Reply with quote

spex074 wrote:
Open Office Calc does not seem to have this option.


That is because it is a different spreadsheet. I'd like our little Honda to have all the options of a Bentley. Unfortunately, it does not. Fortunately, it still has those I need to get from point A to point B. It wasn't quite free, but did save a ton of money. You will see below that it is a matter of choice [I have both and more ..programs, not cars Smile], then a matter of learning the new machine's capabilities.

Hold the ctrl key while dragging down from one cell to the other. Or copy/paste.

With regard to your second question: Top left is a window which reflects the cell name you are in. There you can also type in a range of cells, such as A1:A5000. That range is then automatically selected and you can use the top menu option, Edit /Fill... .

Incidentally, both Calc and Excel behave similarly in that last option, so it's not just the program differences [and there are several and many].

David.
Back to top
View user's profile Send private message
JakeMazz
Newbie
Newbie


Joined: 27 Sep 2009
Posts: 1

PostPosted: Sun Sep 27, 2009 7:34 am    Post subject: Fill Down Reply with quote

Isn't it as simple as:

Tools / Customize / Keyboard tab
Select Cntrl-D
Category: Edit
Function: Fill Down
Select Modify.

What am I missing?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Sep 27, 2009 8:17 am    Post subject: Re: Fill Down Reply with quote

JakeMazz wrote:
Isn't it as simple as:

Tools / Customize / Keyboard tab
Select Cntrl-D
Category: Edit
Function: Fill Down
Select Modify.

What am I missing?

Excellent! In fact, you miss a tiny detail. A question ending with "like in Excel" triggers certain explanations about the fact Calc not being Excel.
The Ctrl+D fill down in Excel pulls down from the previous row without having to select anything. In most cases you want to copy formulas down in order to expand calculated fields into a newly added row of a database-like list.

[Calc, Python] expand/shrink list ranges.. Extract the attachment to one of the two suggested directories and assign two keyboard shortcuts, such as Ctrl+Insert and Ctrl+Delete.
The macro inserts row from "sloppy selection". Select one cell to insert one row into the whole width of the list of adjacent values.
It takes care that all references expand to the new range, copies down all formulas and selects the remaining cells for editing.
The other macro removes rows from a "sloppy selection".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group