gc
Member of DD Central
Posts: 152 
Likes: 141
|
Post by gc on Nov 18, 2016 15:55:57 GMT
Hi guys, does anyone know how to make a column of numbers auto-format itself so that if one deletes a row, the numbers automatically fall into sync?
Example..
1
2
3
4
5
etc...
If you deleted row 3, I would like the numbers to auto-format so that it ends with 4.
RIGHT:
1
2
3
4
WRONG:
1
2
4
5
Thanks for any advice on this..
Btw, I am actually using Libre Office but the formula structure are almost identical so I am pretty sure what works in Excel will work in Libre Office.
|
|
cooling_dude
Bye Bye's for the PPI
Posts: 2,853
Likes: 4,298
|
Post by cooling_dude on Nov 18, 2016 16:07:30 GMT
Hi guys, does anyone know how to make a column of numbers auto-format itself so that if one deletes a row, the numbers automatically fall into sync? Example.. 1 2 3 4 5 etc... If you deleted row 3, I would like the numbers to auto-format so that it ends with 4. RIGHT: 1 2 3 4 WRONG: 1 2 4 5 Thanks for any advice on this.. Btw, I am actually using Libre Office but the formula structure are almost identical so I am pretty sure what works in Excel will work in Libre Office.
Is that the data (1, 2, 3, 4 etc) In your scenario, you could have a simple formula to add 1 from your previous line. Start with "1" in A1 then in A2 "=A1+1" then drag down. Every time you delete a line it will auto format. If your data is more complex then we will need to see some of it 
|
|
Neil_P2PBlog
P2P Blogger
Use @p2pblog to tag me :-)
Posts: 355
Likes: 209
|
Post by Neil_P2PBlog on Nov 18, 2016 16:21:09 GMT
other alternative is =ROW() +- [something] as you might get reference errors with the +1 method
|
|
gc
Member of DD Central
Posts: 152
Likes: 141
|
Post by gc on Nov 18, 2016 16:44:13 GMT
Is that the data (1, 2, 3, 4 etc) In your scenario, you could have a simple formula to add 1 from your previous line. Start with "1" in A1 then in A2 "=A1+1" then drag down. Every time you delete a line it will auto format. If your data is more complex then we will need to see some of it It is only numbers in that cell, other cells within the row have more complex structures going on but won't affect the numbers as it is the entire row that is removed. Thanks for this but after trying it, like Neil said, one is left with "#REF!" errors. Neil, thanks for that but I am a little wet behind the ears with Excel and not fully sure how your formula works. Do you have an example of how to use that?
|
|
gc
Member of DD Central
Posts: 152
Likes: 141
|
Post by gc on Nov 18, 2016 16:53:03 GMT
other alternative is =ROW() +- [something] as you might get reference errors with the +1 method That's pretty close but won't work either. Here is a snippet of the worksheet. (forget the constant row of 1's going down it, that is just to tally the amount of rows with data in and not important in this scenario) 
|
|
Neil_P2PBlog
P2P Blogger
Use @p2pblog to tag me :-)
Posts: 355
Likes: 209
|
Post by Neil_P2PBlog on Nov 18, 2016 16:56:39 GMT
gc - =ROW() just gives the row number. It will be a fixed number off what you want, since you are starting at row 6 from the screenshot, so just try =ROW()-5 and drag that down
|
|
gc
Member of DD Central
Posts: 152
Likes: 141
|
Post by gc on Nov 18, 2016 17:03:55 GMT
gc - =ROW() just gives the row number. It will be a fixed number off what you want, since you are starting at row 6 from the screenshot, so just try =ROW()-5 and drag that down Either you're a genius, or i'm an idiot.. Unfortunately for both of us, I am running with the latter ;-) Thanks Neil, that worked perfectly.
|
|
