This may or may not be what you are after.
This Assumes you know which cells you will be referencing. And remember this is only an example..
IF(A2<TODAY(),D2,INDEX(D,COUNTA(D) + 1))
Using the IF function.
IF(if-expression,if-true,if-false)
First Compare date value in A2 with Todays date TODAY() as the if-expression
A2<TODAY()
We use the less than symbol < to return a true or false for the expression.
If the expression returns True, then we fill the cell with The cell data in D2
If the expression returns False, then we fill the cell with the last with data in column D
The INDEX function returns the value in the cell located at the
intersection of the specified row and column within a collection of
cells or from an array returned by an array function.
.
The COUNTA function returns the number of its arguments that are not
empty.
It is bets you look up in the Functions panel for a fuller explanation on all the functions.
Once you have set up the first row use the drag handle to drag the formula cell down to the other rows you want it on.
They will auto adjust to the correct cell references.
Notes:
- At time of this posting the date was 6 September 2014
- You will need to find a way of dealing with Blank cells in the data column. If a blank cell is found it will throw the results. You should find a resolve with a google search. It will be the COUNTA function that you need to look at.
It seems I've finally figured it out. You need to number all your rows and then select the column which contains these numbers in the organize/sort sidebar. Finally, you choose increasing or decreasing to reverse the rows. This order remains even if you delete the column with the numbers.
Best Answer
In addition to njboot's answer, If you wish to drag and apply the ½ hour increments without entering a formula, be sure to select Both cells before dragging down the yellow dot. This will copy the pattern down (or across, for that matter.)
Edit:
If you want to use a formula:
Use Duration:
DURATION(weeks, days, hours, minutes, seconds, milliseconds)Using Duration one can then adjust the increments later on affecting all cells with one simple change. For example, suppose you wish to change it to 15 minute increments or 2 hour or 25 minutes. This can be accomplished using a dynamic formula to allow for greater flexibility in your solution.
One could add a new table (or use hidden cells or whatever tickles one's fancy) to create an area where a number can be added that will change the increments universally.
Create Duration formula:
Notice that Hours and Minutes are tied to cells that can be adjusted without changing the formula.
Then, after dragging down the formula, just change the increments when desired.