0

I am working to create a function that keeps the data in a cell dynamic until the date is beyond that of the date column. In other words, there is a column of dates, i.e. 9/5 9/6 9/7... and the other columns are tracking data from other fields. If the row date is older than the current date, then the data in the field stops updating and remains at the last known value. Is this possible in Numbers or should I look for another solution?

Thank you! Mike

  • Are you able to post a screenshot. – markhunte Sep 6 '14 at 14:42
  • Thanks Mark. I'll try and give an example of what I am trying to do here, but unfortunately, can't be from what I am currently working on. Basically, I am tracking several values, and on a daily basis, I would like to have those values, "lock-in" so that I can track the progress of those values. Sort of like a dynamic graph, without the graphic. I can, of course, key in the values I want at a particular time each day, and get the result, but I prefer to have this process automated if possible. Seems to be a little trickier in a spreadsheet formula format. – Mike N Sep 8 '14 at 14:57
3

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))

enter image description here


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.

enter image description here

They will auto adjust to the correct cell references.

enter image description here


enter image description here


enter image description here


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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .