How do you make it stop increasing numbers/letters automatically???

If you drag a single cell with a number/letter, it will increment it. If you select two cells that have the same number/letter then drag, it will not increment it. If you select two or more cells that have different numbers/letters then drag, you will get a repeating pattern of those cells.

Hi Alfred,

Here's my test, done just now with Numbers '09 v2.0.3

Columns B, C and D were formatted as indicated by the label at the top of the column. (Column A is a Header column, formatted as Text by default, and was not used. After formatting, a number, 1, was entered independently in each column. (Not filled right, as that would have carried the formatting of the first cell into the others.)

All three cells were then selected as shown above, and the fill control dragged dow to fill rows 3 - 10.

As can be seen, only the Text-formatted column incremented.

"To say that I find this having to select 2 cells to prevent the increase somewhat inelegant would be to add positive spin,"

I don't see it as particularly inelegant.

There are three default behaviours:

• increment by one unit (for single letter text strings, numbers formatted as text, text strings ending in a number, and dates)
• increment by zero for numbers and durations
• do not increment text strings not described in the first set above..

For any value included in the first two sets, you can chose a different increment (including a zero increment for the items described in the first set) using 2 examples.

For any value type included in any of the sets you can set a repeating pattern using the minimum number of values to establish a non-incrementing pattern. (eg. a,b,c or z,y,x will increment a, b, c, d, e... or decrement z, y, x, w, v,,,, but c, a, b will repeat, as will a. b. c. a. b. c )

"Why, I wonder, isn't it possible to just select a format option that prevents the increase from taking place?"

Most likely a design choice.

But there is such a 'format option.' Format the value as a formula:

The text column has had its format returned to Automatic.

Formula in B2 and D2: =7

Formula in C2 (to get text result): ="7"

All three cells selected and filled down as before.

Regards,

Barry

Your value is incrementing because it's a member of the first set as described in my post: "text strings ending in a number"

You can prevent the increment by any of the following actions:

1. Enter "11x14" (no quotes) in each of the top two cells. Select both, then drag the Fill control to fill down.
2. Enter ="11x14" (with the quotes) in the top cell of the column. Fill down.
3. Enter " 11x14 " (no quotes, but do include the space after 14 if the cells will have text aligned left, or both spaces if the cells will have text centered) in the top cell. Fill Down
4. Include the inch symbols in your entry in the first cell: 11"x14" Fill down.

1. This sets a zero increment. The value still increments, but the increment is zero.
2. Each value is set by the equation. When all are set, you may find the table's speed improves if you then Copy the whole column, then go Edit > Paste Values to eliminate the need to constantly recalculate the formulas.
3. Seemed like something that should work (it's the same technique as used in #4), but the spaces are stripped when the value is filled into the next row, even if non-breaking spaces (option-space) are used. Pity.
4. This works as the text string no longer ends with a number. Several other characters could be used, but not a space, a period ('decimal'), or anything else that would let Numbers interpret the end f his text string as a number.

With 25 000 rows to fill, I would not use the Fill control to do the filling. It's going to get awfully slow and tedious.

Fortunately, it's not the only method to fill a column with the same value.

With #2 or #4, you can:

• Enter the value in the first cell.
• Click on the column reference tab to select the entire column.
• Commmand-click on any cells above the one in which you put the value to remove them, one at a time, from the selection.
• With the cell containing the value and all cells below it selected, go Insert > Fill > Fill Down.
  
• for #2 only, with all cells now containing the formula still selected, Copy
• Go Edit > Paste Values

The above steps won't work with #1, and Fill Down fills only the contents of the cell at the top of the column, and the value will increment as it's filled.

For #1, #2 or #4:

• Enter the value in the first two cells.
• Select both cells and Copy
• Click on the column reference tab to select the entire column.
• Commmand-click on any cells above the one in which you put the value to remove them, one at a time, from the selection.
• With the two cells containing the value and all cells below them selected, Paste.
  
• For #2, continue with the two extra steps above.

"Let's see 14" + 26037" =... Well surely you can see how expensive the frame is going to be?"

The frame? The mat itself is going to be expensive enough. I've never seen a piece of mat material over 650 metres long!

One more thing...

Before you commit to doing this with Numbers, I'd suggest making a dummy table with the number of rows of data you expect to reach.

The easiest and most efficient way to do that is to

• fill in one full row of data, Then fill that down to the end of your initial table.
  then..
• Click on a cell in the bottom row, Select All and Copy.
• Add a row, click in it's first cell, Paste
• Scroll to the bottom of the table.
• Repeat the above three steps until you have about 20 000 rows.

Now check the speed of data entry as you add another 'real' record to the table..

Numbers tends to be pretty pokey with large tables, especially if those tables include (multiple) Header rows and columns or Footer rows.

Better to find out if you can live with it before you've entered a few thousand records. 😉

Now if I can find my spear and magic helmet, I'm off in search of that wascally wabbit.

Wegards,

Bawwy

😝

Hello

Dragging a cell containing a number will not increment the value.

If we want to increment the number, we must start with two selected cells.

The numbers will be incremented by the difference between the two starting numbers.

Yvan KOENIG (VALLAURIS, France) mardi 6 janvier 2011 21:01:13

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

KOENIG Yvan wrote:

Hello

Dragging a cell containing a number will not increment the value.

If we want to increment the number, we must start with two selected cells.

The numbers will be incremented by the difference between the two starting numbers.

I was unclear and incomplete in my response.

With cells formatted as automatic or a number format, dragging a single cell that has a number in it will not increment it. With cells formatted as text where the text happens to be a number, dragging that cell will increment the number.

And if you want a repeating pattern, the values in the selected cells can't be in order. For instance, dragging cells with the numbers 1, 2, 3 will give you 4, 5, 6 , etc. Dragging cells with the numbers 1, 3, 2 will give you 1, 3, 2, 1, 3, 2,, etc.

Thanks Badunit

(1) I learnt something.

I didn't knew that a number entered in a text cell was incremented when we drag it.

(2) Numbers in order always define a pattern.

If you start with 1, 2, 3 there is no visible difference because the difference between 1st and 2nd value is the same than the one between the 2nd and the 3rd.

If you start with 1, 2, 4, the pattern will be visible.

Yvan KOENIG (VALLAURIS, France) mardi 6 janvier 2011 21:37:04

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community