Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Prepopulating fields with default dates and values


  • Please log in to reply

#1
Zishan555

Zishan555

    New Member

  • Member
  • Pip
  • 2 posts
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
  • 0

Advertisements


#2
Zishan555

Zishan555

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts
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
  • 0

#3
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts

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!
  • 0

#4
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
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.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP