Shortcut to add 1 to a cell?

Question

Level 1

0 points

Shortcut to add 1 to a cell?

I've got an inventory spreadsheet where I frequently need to add or subtract 1 unit from the cells in the "# of units" column. (if someone buys something or returns something)

Is there a way to create a script / shortcut that would allow me to add or subtract 1 unit to the current value in the cell with a click or keystroke, rather than having to manually change (for example) a 4 to a 5 if I want to add one unit into the inventory?

I found a way to do it in Excel, but I'm trying to never use Office again, and I love my Numbers spreadsheets!

Any help would be greatly appreciated.
thanks!
- Chad

Macbook, Mac OS X (10.5.6)

Posted on May 2, 2009 1:02 PM

Reply

Answer 1

sharknca

Level 1

130 points

May 2, 2009 4:59 PM in response to schrysco

You can try the Stepper or Slider.

Select a cell in the table.
Show the Inspector (blue button on right end of toolbar).
Show the Cells Inspector (one of the button along the top of the Inspector will get you there).
Show the Cell Format menu, and click on Stepper.

The cell will now have a control that will increase and decrease with a click to one of the paddles. You can set the min, max, and increment size in the Cells Inspector.

Free Image Hosting

Robin

Reply

Answer 2

schrysco Author

Level 1

0 points

May 4, 2009 10:20 AM in response to sharknca

Thanks, that's a perfect way to do it! This forum is truly fantastic, I really appreciate your time!

Reply

Answer 3

schrysco Author

Level 1

0 points

May 4, 2009 10:31 AM in response to schrysco

So I've found one little problem with this method-- any cell that contains a "0" or is just empty immediately gets converted to a "1" when I change the format to stepper. Kindof an odd behavior, actually? Any thoughts on how to avoid this?

Reply

Answer 4

KOENIG Yvan

Level 8

41,819 points

May 4, 2009 10:41 AM in response to schrysco

The Inspector of cells may be your friend if you look at it.

At least on my machine, for cursors and steppers, it offers the ability to define the smaller value, the higher one and the step.

Yvan KOENIG (from FRANCE lundi 4 mai 2009 19:41:08)

Reply

Answer 5

schrysco Author

Level 1

0 points

May 4, 2009 10:48 AM in response to KOENIG Yvan

Yes I can see that, but here's the problem:
If I click on a cell that currently has a value of 0 (ZERO), when I change the format to Stepper it changes the value to 1. Now even if adjust Stepper minimum value to 0, the value of the cell remains at 1.
This is a problem because I want to group change hundreds of cells at one time, and many of them have the value 0. So if I change to Stepper all those zeros will turn into ones, and I have to manually change them back. Make sense? try it yourself you'll see what i mean...

Reply

Answer 6

Barry

Level 9

60,023 points

May 4, 2009 12:56 PM in response to schrysco

If your cells are in a contiguous column, you can simplify entering zero values.

Set one cell's format to Stepper, enter the minimum, maximum and increment values, then click on the cell and set the value to zero.

Now grab the fill circle at the lower right of the cell and drag it down the column.

Regards,
Barry

Reply

Answer 7

KOENIG Yvan

Level 8

41,819 points

May 4, 2009 1:45 PM in response to Barry

Hi Barry

It seems that the OP has a column already filled with values and wish to replace them by steppers formatted cells reproducing the values already stored.

As far as I know, there is no way to do that.

My workaround would be to change the format of one cell the next time this one must be changed so, time after time, the entire column will be filled with steppers.
But for serious tasks I would not rely on the steppers formula.

It's too easy to change a value by error.

Yvan KOENIG (from FRANCE lundi 4 mai 2009 22:45:00)

Reply

Answer 8

schrysco Author

Level 1

0 points

May 4, 2009 1:46 PM in response to Barry

Thanks for the response, but I can't do that since the cells are already populated with inventory numbers. If I drag the circle down it will wipe out the data that's currently in those cells.

The problem now is that many cells contain a 0 (zero). For some reason when I convert the format of a cell with 0 as its contents to Stepper, it changes the value to 1. Try it out, you'll see what I mean.
It seems like this happens because the default Stepper parameters are minimum=1.
So every time you try to turn a new cell into a Stepper cell, it sets the default minimum to 1, changing the 0 to a 1.

And like I said, I can't copy that Stepper format to other cells because doing so would erase the current values of those cells.

Reply

Answer 9

Badunit

Level 7

30,648 points

May 4, 2009 2:33 PM in response to schrysco

Yes, it does what you are saying.

Reply

Answer 10

Badunit

Level 7

30,648 points

May 4, 2009 3:37 PM in response to schrysco

It is sometimes hard to redesign a spreadsheet that is already filled with data. Here is what I would do:

Create two new, temporary columns.

Copy/paste your inventory numbers into one of them.

Put the formula =IF(B=C,"", "ERROR") into the other. "B" is the original inventory column and "C" is the new one. This will help you locate the zeros later.

Change your original column into steppers: Select the entire column of numbers and change the cell format to a stepper with 0 as the min and whatever max you want. All cells will have the original number except those that were zeros.

Change one of the incorrect steppers to a zero. Copy (Cmd-C) it. Paste (Cmd-V) it to each of the incorrect ones.

Delete the two temporary columns.

Reply

Answer 11

sharknca

Level 1

130 points

May 4, 2009 6:22 PM in response to schrysco

Is sorting the column an option?

If you sort by the column, then all the cells containing zero should be contiguous. You can copy/paste the Stepper to all the cells containing zero at once and then apply the Stepper as you normally would to the rest of the cells.

Robin

Reply

Answer 12

Barry

Level 9

60,023 points

May 4, 2009 8:13 PM in response to sharknca

"Is sorting the column an option?

Not by itself. All Numbers sorts sort the whole table.

If you sort by the column..."

This you can do, and it should have the results you describe, gathering all the zeroes in one group.

Take care that you have a means of resorting the table to the original order. The easiest is to add a column with the formula =ROW() in all the cells. Copy the result, then Edit > Paste Values. Sorting the table on that column will now restore the order to what it was when the list of numbers was generated.

A combination of this sort and the most recent suggestion by Badunit should solve it.

Regards,
Barry

Reply

Answer 13

May 7, 2009 5:20 AM in response to Barry

+All Numbers sorts sort the whole table.+

Barry,

An improvement in Numbers 09, we can sort just certain rows if we use the Reorganize panel:

Jerry

Reply

Answer 14

Barry

Level 9

60,023 points

May 10, 2009 10:40 PM in response to Jerrold Green1

"An improvement in Numbers 09, we can sort just certain rows if we use the Reorganize panel:"

Thanks for the update, Jerry.

Regards,
Barry

Reply

Answer 15

KOENIG Yvan

Level 8

41,819 points

May 11, 2009 2:23 AM in response to Jerrold Green1

Jerrold Green1 wrote:
+All Numbers sorts sort the whole table.+

Barry,

An improvement in Numbers 09, we can sort just certain rows if we use the Reorganize panel:

We where already able to sort only a range of rows with Numbers'08.
The sort applied to the selected range of rows.
In fact, the new feature just allow us to apply the sort to the entire table even if the selection is smaller.
As the default behavior is 'sort the entire table', from my point of view it's an unsafe feature.
It would be safer if it was defaulting to sort the selection.

Yvan KOENIG (from FRANCE lundi 11 mai 2009 11:23:11)

Reply