I am trying to automate some checks in a .csv file with multiple sheets. I need a formula which will search a specific sheet (Medication sheet) for two column headers (
AgeAtStop). Once found, it will perform an IF statement (
=IF(E2<=F2,"Ok","Not Ok")) on the values of those two columns, making sure that
AgeAtStart is less than or equal to
AgeAtStart Other Column Other Column Other Column AgeAtStop 61.85 x x x 61.98 61.98 x x x 72.22 72.62 x x x 73.74 72.27 x x x 72.57 73.82 x x x 73.85 61.85 x x x 61.98
I'm flexible regarding how the Summary sheet will report this, but my initial thoughts are reporting the number of
AgeAtStart which are greater than
AgeAtStop or returning the position of the cells which contain the incorrect age. I do not want to return results for each IF statement, but rather return a count of those IF checks which returned "Ok" or "Not Ok".
I've tried to accomplish this using a combination of VLOOKUPs and INDEX/MATCH, but so far can't get the logic down. Maybe VBA would be a better solution?
EDIT: Using cybernetic.nomad's implementation works great.
As a follow-up, I am putting this formula in a "Summary" sheet and referencing the "data" sheet. Is there a way to apply this formula automatically "x" number of times in the "Summary" sheet, where "x" represents the number of rows in the "data" sheet? So, if my data sheet has 100 rows, in the Summary sheet, the formula will automatically populate 100 times, referencing each 100 rows.