[Zishan555]

Hi guys, I was wondering if any of you can help with a spreadsheet I'm trying to build. This is actually a couple of smaller problems in one. I'll break it down.

Background: I'm using Excel 2003 on Win XP. I have six columns, A to F, which hold the following titles respectively: Type, Date, Status, Issue, Category, Date Completed.

1. Once I enter an issue into a new row in Column D which can be any value or a number, I would like columns A (type) and E (category) prepopulated with a set value (the same value each time) but which I can change if necessary.

2. At the same time, I would like today's date entered into column B (date).

3. Column C (status) can have lots of values but once the value is set to Done then column f (Date completed) should be prepopulated with todays date.


Once the date is set then obviously it shouldn't change so should be stamped rather than a formula. I've tried a mixture of vlookups and macros but haven't made much progress. If anyone can help with any of those parts or even the whole thing then that would be awesome. I have some macro writing experience but this is proving to be beyond me.

Thanks in advance all.
~Z

[Advertisements]

FYI I've also posted this question on the following forum so anyone about to answer can check what other things i've already been told! I think this might be helpful:

http://www.excelforu...and-values.html

[Zishan555]

FYI I've also posted this question on the following forum so anyone about to answer can check what other things i've already been told! I think this might be helpful:

http://www.excelforu...and-values.html

[Vino Rosso]

1. Once I enter an issue into a new row in Column D which can be any value or a number, I would like columns A (type) and E (category) prepopulated with a set value (the same value each time) but which I can change if necessary.

In the cells of column A and E:
=IF(Dx="","",<value>)

2. At the same time, I would like today's date entered into column B (date).

As you say, entering TODAY() will update the date each time the spreadsheet is opened/calculated.
I'm not aware of an Excel Function that enters a date that doesn't update.
This can be done in two ways:

• Macro - but I think you can't run a macro based on a Function i.e. =IF(Ax="","",<run macro>).
  So you would basically have to run the macro manually to enter the date. Much simpler however...
• Manually - enter the date directly into the cell with Ctrl+; (doesn't update)

3. Column C (status) can have lots of values but once the value is set to Done then column f (Date completed) should be prepopulated with todays date.

See my points made under 2 above.

Having said all that, others may have a better idea!

[Jonesey]

If you have experience of writing macros, try using the built-in "Worksheet_SelectionChange" macro.

This will run every time your spreadsheet is changed, which should help you to solve your pre-population problem.

This macro can alse be used to insert a non-formula based date, based on the NOW function.

Your macro could have a couple of tests built into it so that, if column B is already populated, changing the spreadsheet won't update it again and inserting today's date.

Edited by Jonesey, 16 July 2009 - 06:23 AM.