Adding dates to a range of cells based on certain criteria

Hi all,

I would like to automate adding dates to certain cells based on what another cell contains. This is a task management spreadsheet and when one field is set to 100% I would like to automatically add the date that cell was changed to 100% in another cell. There is a range of cells up to 500 that would all need to check the cell adjacent to them for the 100% value.

I have tried to accomplish this with nested if statements but it becomes a circular reference or it changes the date every time the spreadsheet is refreshed.

For further clarification I have attached an image showing a flowchart of my logic: Drawing1.jpg

Thank you!

Hi

How about a sheet event.

Please Login or Register  to view this content.

rylo

rylo,

Thanks! I was able to get that working in a less elegant way.

Please Login or Register  to view this content.

I am now trying to get the same code to work for a range of cells now. I'll probably mess around with yours and see if I can increment it in a for loop. I was also thinking about counting before hand how many I would need to do the for loop for and using that value as the max instead of just making the max a really high value.

If you have any feedback on that I'd appreciate it!

This makes sense to me, but not to Excel:

Please Login or Register  to view this content.

Hi

I presume that the G and J are meant to be columns? If so then

Please Login or Register  to view this content.

rylo

ah ha! I was doing the rows and columns backwards.

Thank you for your help!