Autoformat Number for Next row

[Donal28]

Hi All

I'm looking to autoformat a spreadsheet where in column A the numbers will automatically increment by 1 when the user click into the next row(cell) in column A. Have been looking at the autoformat option but don't see anything that seems to do this. Any help on this would be very much apprecaited

Regards
Donal

 

[GTO]

Hi there,

When they click into the next row from where? What I mean is that if the press Enter, and are on the last row with a number in Col A, this seems easy. But what if they click (mouse) and skip rows. What if they are in some faraway column? Do you have selection locked?

Mark

 

[Donal28]

Hi there,

When they click into the next row from where? What I mean is that if the press Enter, and are on the last row with a number in Col A, this seems easy. But what if they click (mouse) and skip rows. What if they are in some faraway column? Do you have selection locked?

Mark

THanks for the reply GTO, spreadsheet has rows A to I populated so If I had columns 1 to 6 populated and I was in column I in row 6 when I hit enter I would like to start a new row(in Cell A7) and 7 to appear in column A of row 7...hope this makes sense!

Donal

 

[njfeathe85]

Assuming you have the following columns:

Order Number (Auto format)| Client | Address

Create another column out of the way (in this case in column G) that you can hide later and call it "Empty Cell"

In the first cell enter

=COUNTBLANK(B2:B2)

and then hover over the bottom right corner of the cell until a cross appears, click and drag down over the cells below for as for as you need.

Then go to the Order Number and in the first cell enter the following:

=IF(G2=1,0,1)

In the cell below that enter

=IF(G3=1,0,A2+1)

Then as you did with the Empty cell, fill the cells below in the same away for as long as you did the empty cell column.

Then fill in your client data etc. The empty cell column will recognise that the client cell is no longer and then change the cell, this will be recognised by the test in the IF statement and then give the result of adding a new number.

Hope this helps.

<table border="0" cellpadding="0" cellspacing="0" width="982"> <col style="width: 147pt;" width="196"> <col style="width: 96pt;" span="5" width="128"> <col style="width: 110pt;" width="146"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 147pt;" class="xl63" height="20" width="196">Order Number</td> <td style="width: 96pt;" class="xl63" width="128">Client</td> <td style="width: 96pt;" class="xl63" width="128">Address</td> <td style="width: 96pt;" class="xl63" width="128">
</td> <td style="width: 96pt;" class="xl63" width="128">
</td> <td style="width: 96pt;" class="xl63" width="128">
</td> <td style="width: 110pt;" class="xl63" width="146">Empty Cell</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G2=1,0,1)</td> <td>Joe B</td> <td>Test Test</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B2:B2)</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G3=1,0,A2+1)</td> <td>Joe B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B3:B3)</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G4=1,0,A3+1)</td> <td>Joe B</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B4:B4)</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G5=1,0,A4+1)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B5:B5)</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G6=1,0,A5+1)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B6:B6)</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G7=1,0,A6+1)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B7:B7)</td> </tr> <tr style="height: 24.95pt;" height="33"> <td style="height: 24.95pt;" height="33">=IF(G8=1,0,A7+1)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>=COUNTBLANK(B8:B8)</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody> </table>

 

[Donal28]

Assuming you have the following columns:

Order Number (Auto format)| Client | Address

Create another column out of the way (in this case in column G) that you can hide later and call it "Empty Cell"

In the first cell enter and then hover over the bottom right corner of the cell until a cross appears, click and drag down over the cells below for as for as you need.

Then go to the Order Number and in the first cell enter the following:
In the cell below that enter Then as you did with the Empty cell, fill the cells below in the same away for as long as you did the empty cell column.

Then fill in your client data etc. The empty cell column will recognise that the client cell is no longer and then change the cell, this will be recognised by the test in the IF statement and then give the result of adding a new number.

Hope this helps.

Thanks for your help yes this does work but is there anyway to hide the O's in the Order number field until it is populated with a legitimate row number

 

[njfeathe85]

Thanks for your help yes this does work but is there anyway to hide the O's in the Order number field until it is populated with a legitimate row number

Yeah Just use Conditional Formatting

Highlight the column>click conditional format>choose format only cells that contain>in the first drop down box choose 'cell value'>in the 2nd choose 'equal to'>in 3rd type '0'>click on format>choost the 'font' tab>select 'white' from the drop down box titled color>Click Ok>Click OK

Job done

 

[Donal28]

perfect thats worked...thanks alot....also figured out if i use =IF(FG3=1," ",A2+1) for the second formula and drag it down it also works

 

[njfeathe85]

perfect thats worked...thanks alot....also figured out if i use =IF(FG3=1," ",A2+1) for the second formula and drag it down it also works

Not a problem