If No is deleted in middle of sequence,Nos alter after deleted No
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 . |
If No is deleted in middle of sequence,Nos alter after deleted No
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 HIS "crusty53" wrote in message ... 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 . |
If No is deleted in middle of sequence,Nos alter after deleted No
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 |
If No is deleted in middle of sequence,Nos alter after deleted No
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 |
All times are GMT +1. The time now is 03:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com