Hello,

I am trying to work out how many it has been since a certain criteria has been met.
At the moment I input data each day and a macro extracts the data and pastes the required info into cells H5:H24. The date of the info is also pasted into H3.
The next day I repeat the process and a macro inserts a new column as column "H" and pastes the new data the same as before; the old data is moved to column "I" and so on each day.

I have put in a simple IF statement in cells below stating if the data entered is above a target value. If >=500,1,0.

After a few days I have several lines of data and several lots of 1s and 0s from the IF statements.

Is there a formula which searches the 1s and 0s (lets say in they are in H40:H59 for a day and that these too move each day), find the first 1 in a row of data and then tells me the day it relates to (H$3)???

I can then use this date vs todays date and work out how many days it has been since the criteria was hit.

Note: The data runs in rows, the ranges in columns are the results for different people.


Summary Example:

Search H41:P41 for newest 1 then give data from relevant column$3

THANKS!