Saving a continuously changing cell value to another cell, once only.

Hi, I'm hoping someone can help with my problem.

Say I'm working with Columns A, B and C and Rows 1-10.

Each cell in Column A is linked to continuously changing numbers in another part of the sheet (usually all different numbers).

I have a formula in the corresponding cell in Column B looking for certain requirements to match, one of which is the number in its corresponding cell in Column A. If all requirements match then the cell in Column B says "yes".

The cell in Column B will keep changing from "yes" to "no" depending on whether I have a match. All I want is for the corresponding cell in Column C to record the value from the corresponding cell in Column A when the corresponding cell in Column B says "yes". But I only want it to do this on the very FIRST time that the corresponding cell in Column B says "yes". So if it goes "yes", "no", "yes" with different values in A, I only want it to keep the value from the first "yes".

So when I have that value saved in C for whichever cell it is (1-10) then that's it, job done for that cell. But I then still need it to carry on looking for "yes" in the other cells in B and doing the same thing.

My VBA is very limited and I've only succeeded in having the number appear and change or not appear at all. I've also managed to make the sheet crash numerous times.

Any help would be greatly appreciated.

Thank you.

Thank you so so much MrShorty not only for taking the time to read and understand my description perfectly, but also for coming up with the perfect solution. It works exactly as I wanted and not having to use VBA is a bonus. Thanks again.