If No is deleted in middle of sequence,Nos alter after deleted No

[crusty53]

I have a group of numbers,I delete one number in the middle ,i want the
numbers
after that number to reduce by one so that numbers still run in order.How do
I do this.Thanks in advance .

[Nick Hodge]

Crusty

If you are deleting a complete row then you could set up a formula referring
to the cell above, (in A2 for example),

=A1

Now when you delete the row the formulae will update in line

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"crusty53" <[email protected]> wrote in message
news:[email protected]...
>I have a group of numbers,I delete one number in the middle ,i want the
> numbers
> after that number to reduce by one so that numbers still run in order.How
> do
> I do this.Thanks in advance .

[broro183]

Hi Crusty,

I'm not sure if I understand correctly but...
If these are all consecutive descending #'s in a column, try entering something like the below in cell A8:
=min($a$1:a7)-1

If these are all consecutive descending #'s in a row, try entering something like the below in cell F1:
=min($a$1:E1)-1

In other words:
=MIN(first cell in list with dollar signs : cell immediately before current cell w/o dollar signs) - 1

I have used this technique with "max($a$1:a7)+1" for ascending numbered sets of instructions.
Provided the absolute reference (ie $a$1) is not in the row/column deleted, the list will update after a row is deleted.

Nick, if I've understood Crusty correctly, your suggestion =A1 with a "-1" (ie =A1-1 would result in #REF error if row 1 was deleted which would upset all rows after that.


hth
Rob Brockett
Always learning & the best way to learn is to experience...

[Pete_UK]

Another way is to use the formula:

=ROW()

and copy down. If you want the number sequence to start at say 12 in
row 3, then use:

=ROW() + 9

in the first cell and copy down.

Hope this helps.

Pete

broro183 wrote:
> Hi Crusty,
>
> I'm not sure if I understand correctly but...
> If these are all consecutive descending #'s in a column, try entering
> something like the below in cell A8:
> =min($a$1:a7)-1
>
> If these are all consecutive descending #'s in a row, try entering
> something like the below in cell F1:
> =min($a$1:E1)-1
>
> In other words:
> =MIN(first cell in list with dollar signs : cell immediately before
> current cell w/o dollar signs) - 1
>
> I have used this technique with "max($a$1:a7)+1" for ascending numbered
> sets of instructions.
> Provided the absolute reference (ie $a$1) is not in the row/column
> deleted, the list will update after a row is deleted.
>
> Nick, if I've understood Crusty correctly, your suggestion =A1 with a
> "-1" (ie =A1-1 would result in #REF error if row 1 was deleted which
> would upset all rows after that.
>
>
> hth
> Rob Brockett
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=553470