Solved Drop-Down Lists Incorporated into Functions

July 18, 2012 at 08:07:25
Specs: Windows XP
Hey guys,

I'm wokring on a spreadsheet and I've run into a problem.

I have a list set up that tracks the submission of a file and an according timeline to follow based upon that submission date.

I'm trying to make a modifier to update the spreadsheet in the event that something is finished ahead of schedule.

Example:
Person submits file on 1/1/2012
Review need to be finished by 2/18/2012, but is finished on 2/4/2012

I want to make a box that has a drop down menu with "Working" and "Completed" options.
If the box is switched over to Completed, I want to:
Update the box with the due date of 2/18/2012 to whatever the current date is of completion.

This will trigger all the following boxes and update the spreadsheet.
I have made the drop-down menu, but I can't seem to find the right formula for the cell to read from the drop-down menu.
Any help is appreciated.


See More: Drop-Down Lists Incorporated into Functions

Report •


#1
July 18, 2012 at 08:20:22
Let's make sure we are using the correct terms so that we all understand what we are talking about.

You seem to be using the word "box" in 2 different manners:

1 - If the box is switched over to Completed
2 - Update the box with the due date of 2/18/2012 to whatever the current date is of completion.

For Number 1, do mean that the user pulls down the Drop Down in a cell and chooses "Completed"?

For Number 2, do you mean that the cell with the due date should be changed?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 18, 2012 at 08:34:31
My mistake, let's use this as an example:

Cell C5 is the input cell for the user, they input 1/1/2012

Cell C10 is the calculated cell, showing the date 2/18/12, 48 days later, which is when the document is due for review.

I want to put in a drop-down menu in cell D10 as a "status" of the report.
D10 will have both "Working" and "Completed" options in the drop-down menu.

If the review was finished ahead of schedule, say 2/4/2012, and the D10 drop-down menu is changed over to "Completed" I want the C10 cell to update to whatever date D10 was changed.

The updated date will trigger the formulas for the rest of the spreadsheet.

Problems:
I cannot get C10 to read the D10 drop-down menu.
I know the "TODAY()" function won't work, and am not sure if there is a function that just pulls up the current date the field was changed.

Sorry for the confusion, hope this clears it up.


Report •

#3
July 18, 2012 at 09:37:49
✔ Best Answer
This macro will hard code the current System Date in C10 when D10 is changed to Completed.

Note: It will replace the formula.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$D$10" Then
  If Target = "Completed" Then
   Range("C10") = Date
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question