help please! in excel need to add +1 to the next row

heggie

Hi guys, this is probably very simple, i need to add +1 to a row, each row in a spreadsheet. for example if A1 is 1405 then when the user goes to A2 it automatically write 1406 and so on. thanks in advance for any help.

D

try "=A1+1"

heggie

nope that doesnt seem to do it, i need it automatic, not have to do a1, a2 - a1000 everytime i need a new number

Khannie

You'll need a macro to do that. Not big up on macros myself.

Ponster

Err, you just do what D said and copy and paste that into every cell where you'll want a result.
That's what I understand the answer to be from your question.

heggie

yes but that would take a long time, its really jsut to have something automated for job numbers to stop duplicates etc. i mean if i had to write that in, i may as well just type the number in myself.

zAbbo

no you wouldn't, spend 5 minutes reading the excel help on formula's.

Drag the forumla for every row you want it entered.

in A2, enter in =a1+1

now select the bottom right of a2, until a black cross appears, drag this from a2 till a1000.

Now just enter whatever number into a1, and will do the rest for you.

Silly question really, its one thing being told the answer(D already answered it), and another knowing how to apply it

TimTim

Get the first few cells in the pattern you want them to be in.

A1=1
B1=2
C1=3
etc.

Or like in the picture

1.PNG

Select the corner box I've marked out and drag it out. Until you get something like the following:

2.PNG

Should do it for you.

And they say the ECDL is a waste of time.

EDIT: Looks like baz beat me to it.

heggie

ok thanks i'll try that. its not a silly question when you genuinely dont know the answer, but i'll be sure to point that out to you next time your asking something.

Oriel

I've just done it here in work: very simple.

1. In the first field type: "0"
2. Click the field beneath. Go up to the top bar and type '=' now click on the first field. Click back into the bar and type "+1".
3. The second field will now have 1 in it.
4. Click on the second field, and drag as far as you want the list to go.
5. Hit Ctrl-D.

S.

edit - or download this one I did: here.

zAbbo

heggie wrote:

ok thanks i'll try that. its not a silly question when you genuinely dont know the answer, but i'll be sure to point that out to you next time your asking something.

It is a silly question when you've already been given the answer, but apparently you want it spoon fed

di11on

A little enhancement to this, which I think would achieve what you're looking for is something like this

In this example - the order number will only be filled in once you've entered something in the Order details field.

1. Enter the formula: =IF(B3="","",A2+1) once in cell A3. Press ENTER
2. Copy cell A3 by reselecting it and pressing CTRL+C.
3. Then select the entire range from cell A3 downwards. This can be done by selecting cell A4 then pressing SHIFT+CTRL+DOWN_ARROW
4. Paste formula into selected range by pressing CTRL+V

Now, an order number will only appear in column A if there is some text in the cell to the right (in column .

You'll get an error if you skip a row (there will be no number in the row above to add 1 to)

I've attached a sample workbook which has this done.

Hope this helps

The_Thing

Click this link for all things Excel related