If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
filling intermittent blank cells with last value
I have a number of columns of data.
All cells in a column are filled intermittently. How do you fill cells with the last value shown. |
#2
|
|||
|
|||
filling intermittent blank cells with last value
Hi John -
Assuming your data goes from row 2 to at most row 1000, insert this formula in cell A1; don't type the curly brackets, just the part within them, then hold down Ctrl+Shift while pressing enter. If you do this properly, it creates an array formula, which Excel indicates with the brackets. {=INDEX(A2:A1000,MAX(ROW(A2:A1000)*NOT(ISBLANK(A2: A1000)))-1)} - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ john wrote: I have a number of columns of data. All cells in a column are filled intermittently. How do you fill cells with the last value shown. |
#3
|
|||
|
|||
filling intermittent blank cells with last value
John -
A second interpretation. Do you want to put the last value in any subsequent empty cells? Select the entire range, then press Ctrl+G, click on Special, and choose the Blanks option button. This selects just all the blank cells in the range you had selected. Press the Equals key, then the up arrow, then Ctrl+Enter. This puts a formula in each cell which takes the value from the cell above (which in turn, may take it from the one above that, etc.). - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ john wrote: I have a number of columns of data. All cells in a column are filled intermittently. How do you fill cells with the last value shown. |
Thread Tools | |
Display Modes | |
|
|