I need some help using a list.

  • Thread starter Thread starter gregw74
  • Start date Start date
G

gregw74

Alright Excel gurus, I implore your help. Please.

1. I have a cell, for this example, A1. Still with me?? Good.

2. I also have another cell, A2. A2 contains a list created via Data
Validation. The list contains three values (On Track, Behind Schedule,
and On Hold).

My goal is that when A1 has any value (anything > 0), A2 will select
the first value in the list (On Track, as the default selection).

If A2's value needs to change to something else in the list, a user may
manually select one of the other remaining two values (Behind Schedule
or On Hold) from the pull down.

Please let me know of any suggestions you may have.

Thank you!
 
I'd just plop the value in when the cell in column A changed.

Rightclick on the worksheet tab that should have this behavior and select view
code.

Paste this in:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

'don't overwrite any existing value????
If Target.Offset(0, 1).Value <> "" Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
If .Value > 0 Then
Application.EnableEvents = False
.Offset(0, 1).Value = "On Track"
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Then back to excel and test it out.
 
Thank you so much for your response. The problem is, this is going t
happen not only for A1 and A2 but all the way down the spreadsheet. (B
and B2, C1 and C2, etc,) Any further suggestions? I was hoping a fomul
could be used via Data Validation
 
What does etc mean?

Are you saying that a change in column A affects B. A change in C affects D,
E&F, G&H?

But never A&B and B&C--the data is always grouped pairwise in columns?????

if yes, then change this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
to something like:

If Intersect(Target, Me.Range("a:a,c:c,e:e,f:f")) Is Nothing Then Exit Sub

Include just the columns that get the typing.
 
I'm sorry Dave. I gave you some wrong information. I must have been
having a bad day and what few synaptic gaps I have, must not have been
firing.

The redundancy is:
A1 B1
A2 B2
A3 B3
A4 B4... and so on.

The relationship between the two cells would occur all the way down
columns A and B.

Thanks again for your help, and patience.
 
If I'm reading this correctly, then the user types into column A and column B
will change.

If that's true, then I think the first suggestion will work ok.

This portion:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
Says to only look for changes in column A.

And this portion:
..Offset(0, 1).Value = "On Track"
plops the words "On Track" in to the cell one to the right (column B).

Did you try it and have trouble?
 
[image: http://my.voyager.net/~robw/excel/Excel_Example.gif]

This is a sample of my spreadsheet.

When a user enters a percentage into, say, U4 (1.28%) column S4 changes
to "In Progress...". As soon as this occurs the list (provided via Data
Validation) changes to "On Track" in cell T4.
After this the user may change the value via the pull down list to
either Behind Schedule or On Hold.

Any chance it could be done via a formula in T4 through Data Validation
or would a script be best?

Thanks again.
 
Unfortunately, I didn't have any luck with the code you had suggested.
Hopefully it wasn't do to an error on my behalf.
 
I'm confused over what columns you type "On Track" into.

When I looked at the .gif, it was column S.

This is the line that tells the macro what column to look for changes:

If Intersect(Target, Me.Range("a:a,c:c,e:e,f:f")) Is Nothing Then Exit Sub

If it's just column S that gets these values, make it look like:

If Intersect(Target, Me.Range("s:s")) Is Nothing Then Exit Sub

If it's more columns, just keep adding them so they look like the first example.
 
Thank you so much for the suggestion but I'm still not having any luck
I would like to make a revision to my original problem.

This is what I would love to happen (this may make things a littl
easier):

Whenever a value in column S ("Current Status") changes to "I
Progress..." the cell's value one to the right is set to "On Track
automatically. Then, if it needs to be changed later by the user the
may click on the pull down and choose a different value. The file stil
reflects what is seen in the screenshot.

'Excel Screenshot' (http://my.voyager.net/~robw/excel/Excel_Example.gi
)

Thanks for any further suggestions you may have. I just wish I kne
more of how VBA works in Excel. By the way, any suggested resources
 
I'm not sure what you're currently using and where you placed the code.

Make sure it is behind the worksheet that should have this behavior.
rightclick on the worksheet tab and select view code.

If you don't see it in the code window, it's not in the correct spot.

If it is in the correct spot, paste your existing code in your next message.
 
Back
Top