i've seen this done with a LOOKUP(), i think, but here's my approach

This approach is sensitive and assumes that the different sheet has the same structure as Sheet1

But if you put this formula in any cell in Row 2 in your second sheet, and enter it with CTRL+SHFT+ENTER it will work.

=IF(COUNTIF(Sheet1!$A$2:A3,6)<2,"Not Enough 6s",IF(Sheet1!A3=6,ROW()-MAX((--(Sheet1!$A$2:A2=6))*ROW(Sheet1!$A$2:A2)),"cell not 6"))

This cell can then be copied down and will work.

## Like this thread? Share it with others