Formula to insert date stamp when range of columns edited

**fionnuala** · 09-24-2012, 05:54 AM

I'm trying to work out how to create a formula that inserts a date stamp in column Y when any cells from column A to column W are edited in that row. I am aware of other discussions about how to do this for a single column, e.g. http://www.excelforum.com/excel-gene...=1#post2716586

but I haven't been able to work out how to modify this for a range of columns.

I'd prefer to use a formula because using VBA means I lose the Undo function.

Is anyone in a position to advise?

Thank you

**Fotis1991** · 09-24-2012, 06:01 AM

Hi

Try to compine my suggestion in that thread, with COUNTA, function.

Or else, pls upload a small sample workbook.

**fionnuala** · 09-24-2012, 07:05 AM

Thank you for responding!

I gave your suggestion a shot but I am a bit out of my depth, I wasn't sure how to incorporate COUNTA and everything I tried returned either 0 or an error message.

I have attached a small sample of my spreadsheet if you have time to take a look?

Thank you

**Fotis1991** · 09-24-2012, 08:42 AM

If i understand well your request,you want IF all the cells in columns A:W, are not empty, then in column Y to get the date.

So in this case, in Y2 and copy down, put this formula.

Please Login or Register  to view this content.

Be sure that:

Choose Tools/Options/Calculation (Preferences/Calculation for Macs) and check the Iteration checkbox. Must be 1. Not 100!

http://www.mcgimpsey.com/excel/timestamp.html

**fionnuala** · 09-24-2012, 12:01 PM

Sorry to be a pest but what I actually want is to have the datestamp inserted for a row when any of the cells in columns A to W are edited. I cannot rely on only getting the datestamp when all the cells are populated, because they are unlikely to all be populated for all the rows.

Does that make sense?

I can't open your attachment at work, I will open it from home this evening!

Thank you very much for your efforts!

**Fotis1991** · 09-24-2012, 12:04 PM

Then you will try.

=IF(COUNTA(A2:W2)<>1,"",IF(Y2="",NOW(),Y2))

**fionnuala** · 09-25-2012, 06:15 AM

I'm sorry Fotis, I understand if you don't want to help me anymore; that formula does not do what I want it to. I want to achieve what this VBA code will do, but without losing the Undo function:

Please Login or Register  to view this content.

i.e. I want to timestamp any row that has been changed between columns A and W, I want this timestamp in column Y and I want to apply this up to row 95683. I can do this with VBA but it means I lose the Undo function.

I didn't write that code myself so I don't really know what it means, I found it here http://www.ozgrid.com/forum/showthread.php?t=152807

**Fotis1991** · 09-25-2012, 06:32 AM

You are so kind that there is no reason to don't want to help you.

I do. But....

I don't know anything about VBA codes.

Hope someone else to helps you.

**fionnuala** · 09-25-2012, 06:37 AM

Thank you for your efforts anyway!

Formula to insert date stamp when range of columns edited

LinkBack

Thread Tools

Rate This Thread

Display

Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Re: Formula to insert date stamp when range of columns edited

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions