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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 .
 
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
(e-mail address removed)
 
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...
 
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
 
Back
Top