|
How: automatically re-adjust number series
I have the following number series:
B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. |
automatically re-adjust number series
in b2=row()*5+1000 and copy down
Then DELETE the blank row(s) Don Guillett Microsoft MVP Excel SalesAid Software "Josh W" wrote in message ... I have the following number series: B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. |
automatically re-adjust number series
Sorry Don, that doesn't work at all. 1) I do NOT want to delete the row which
contain other data it's only the cell that is blank. 2) Even when I delete a row e.g B2, B3 does not autimatically change from 1010 to 1015. "Don Guillett" wrote: in b2=row()*5+1000 and copy down Then DELETE the blank row(s) Don Guillett Microsoft MVP Excel SalesAid Software "Josh W" wrote in message ... I have the following number series: B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. |
automatically re-adjust number series
See if this works fo you:
B1 = manually entered 1000 Enter this formula in B2 and copy down as needed: =COUNT(B$1:B1)*5+1000 -- Biff Microsoft Excel MVP "Josh W" wrote in message ... Sorry Don, that doesn't work at all. 1) I do NOT want to delete the row which contain other data it's only the cell that is blank. 2) Even when I delete a row e.g B2, B3 does not autimatically change from 1010 to 1015. "Don Guillett" wrote: in b2=row()*5+1000 and copy down Then DELETE the blank row(s) Don Guillett Microsoft MVP Excel SalesAid Software "Josh W" wrote in message ... I have the following number series: B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. |
automatically re-adjust number series
That causes a circular reference?
-- Biff Microsoft Excel MVP "Don Guillett" wrote in message ... or to not show number at blank =IF(TRIM(B2)="","",COUNT(B$1:B1)*5+1000) -- Don Guillett Microsoft MVP Excel SalesAid Software "T. Valko" wrote in message ... See if this works fo you: B1 = manually entered 1000 Enter this formula in B2 and copy down as needed: =COUNT(B$1:B1)*5+1000 -- Biff Microsoft Excel MVP "Josh W" wrote in message ... Sorry Don, that doesn't work at all. 1) I do NOT want to delete the row which contain other data it's only the cell that is blank. 2) Even when I delete a row e.g B2, B3 does not autimatically change from 1010 to 1015. "Don Guillett" wrote: in b2=row()*5+1000 and copy down Then DELETE the blank row(s) Don Guillett Microsoft MVP Excel SalesAid Software "Josh W" wrote in message ... I have the following number series: B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. |
automatically re-adjust number series
I put 1000 in b1 and the formula in b2 and copied down.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... or to not show number at blank =IF(TRIM(B2)="","",COUNT(B$1:B1)*5+1000) -- Don Guillett Microsoft MVP Excel SalesAid Software "T. Valko" wrote in message ... See if this works fo you: B1 = manually entered 1000 Enter this formula in B2 and copy down as needed: =COUNT(B$1:B1)*5+1000 -- Biff Microsoft Excel MVP "Josh W" wrote in message ... Sorry Don, that doesn't work at all. 1) I do NOT want to delete the row which contain other data it's only the cell that is blank. 2) Even when I delete a row e.g B2, B3 does not autimatically change from 1010 to 1015. "Don Guillett" wrote: in b2=row()*5+1000 and copy down Then DELETE the blank row(s) Don Guillett Microsoft MVP Excel SalesAid Software "Josh W" wrote in message ... I have the following number series: B1=1000 B2=1005 B3=1010 B4=1015 B5=1020 B6+1025 etc etc all in increments of 5. However, I have some completely random cells in the same column that I must leave blank. Since I used the fill handle to copy down the number series, how can I delete random cells and have the number series readjusted automatically e.g as follows: B1=1000 B2=blank B3=1005 B4=1010 B5=blank B6=1015 Greatly appreciate your help. Excel 2003. |
| All times are GMT +1. The time now is 02:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com