Is it possible for cells to automatically date themselves when edited

Hi,

I am creating a tracking and monitoring tool for my school.

Within this tool is a sheet where teachers record the level that each child is working at for a particular area- in this example the area is maths.

I have created drop down menus so that staff can select the colour that pupils have achieved, however I need a date for when levels have been achieved. However, I would prefer not to have to create another column where teachers then must add a date.

Is it at all possible for the cell to automatically display the date of when a new cell colour has been selected?

I doubt it but always worth asking you experts if it is a possibility.

Please can you let me know ASAP as I am supposed to be sharing my ideas with colleagues by the end of this week.

Thank you very much for your support

Hello Kev:
I believe you should rethink your approach. Currently the cell has the name of the color within it. If you change this so that this value is the date then you'd loose the name of the color. And loosing the color name means you loose the ability to sort/filter/count/etc by the level the student is currently at.

Though also don't believe a color system is a good approach to grade individuals as colors really don't have a clear objective meaning while a grade does. Or words like wip,complete,not yet all have clear meanings.

Anyway ... that's my two cents.

Thanks nimrod.

We need to use colours as these link in directly to progression pathways that we have created for each curricular area. I have attached an example of a progression pathway to explain what I mean. Think I might just need to create a second column next to each colour?

Thanks Ron, I think I am following you here. Is there no way that we could just get it to display the date and not the colour name also?

If not, then Any chance you could do a couple of cells for me with what you proposed so that I fully understand- I am very basic when it comes to excel.

What is a VBA- would there be any issues with using this?

Sorry, I deleted sheets that I thought were not relevant to this topic but now realised that one of the sheets contained a lookup table needed for the cells. Yes a pop up window with the dates would be fantastic!!! We would only need to see the date if we were worried that a child was not making clear progress and so then need to check how long he has been at a particular level.

Please find attached an update spreadsheet that should have working drop down menus.

I cant thank you enough for your support.

Here's what I did with your file.

• I assigned a range name to the color input area: rngColorInput
• I added VBA code to the Worksheet_Change event that, based on the cell value: Adds/Edits/Removes the cell comment

This is the VBA code:

Please Login or Register  to view this content.

When you open the file, you'll be prompted to allow macros...Allow them!

Please, test the input area and let me know if it works as expected.

A sincere apology everyone. I am quite new to this and did not think about the problems that may have been caused by adding two posts the same. I was simply trying to spread the word- incase people were only reading one forum- in the hope to find a solution.

Ron- the problem was not solved on the other thread so your work has not went to waste. Thank you very much- your solution seems to work perfectly.

Lesson learned for me here.

Sorry for any trouble I have caused and thank you again for finding a solution.