Data Validation Lists

[inkserious]

I currently have two data validation list referenced against the days of the month.

When the main list is changed, data within the worksheet is changed to reflect that day.

I need the second list to automatically default to 7 days prior when the 1st list is changed. I still need for the user to be able to change the second list; however, when ever the 1st list is changes, the second list will again automatically default to 7 days prior.

Thanks for any help.

-ep

 

[Foamy]

i couldn't fully understand the problem, the 2 data validation are list type?

I need the second list to automatically default to 7 days prior when the 1st list is changed.

that makes me think you did smthg like this for the validation:

column A

1/1/2006
2/1/2006

and so on

for column B (the second list) u put a formula that rests 7 days from its column A pair

I still need for the user to be able to change the second list; however, when ever the 1st list is changes, the second list will again automatically default to 7 days prior.

that would make sense with my interpretation. given u have a formula for the second list, the values on the second list will always be 7 days prior...

is that interpretation correct?

B. F. Skinner - "The real problem is not whether machines think but whether men do."

I KNOW I DON'T...

 

[SkipVought]

Hi,

Is your list EMBEDDED in the validation object or REFERENCED by the validation object?

A concrete example of your lists and what you want changed would sure be helpful.

Exactly what EVENT would trigger this change?

Skip,

_[red][/red]

 

[inkserious]

Both lists are referenced by the validation object. Say for example A8:A38 contain July 1 - July 31.

In order to define things more clearly. Let's call the main list "List A" and the secondary list "List B" The user would select a date in list A in order to see data withing the worksheet for that particular day. The worksheet references the data from a separate worksheet via HLOOKUP.

When the user selects a date in List A, List B would automaticall default to List A minus 7. Thus allowing the user to compare the date selected in List A with data from the previous week. ie. Friday July 15 against Friday July 8.

But, the user may want to compare a different date agains the date selected in List A. So they could change the date in List B to say Friday July 1. Allowing the user to compare Friday July 15 against the Friday 2 weeks prior - July 1.

The reason to have List B always default back to List A when it is changed is so that the user does not accidentally forget to change List B to a comparable day. The user will always need to compare Friday to Friday, or Monday to Monday for example.

If they select Saturday July 15 in list A, and then List B automatically defaults to July 8, this ensures that the user is looking at the correct info.

I hope this clarifies the concept behind what I'm trying to accomplish.

-ep

 

[SkipVought]

In the Worksheet_Change event on the sheet containing the Validation Object, assuming that A1 contains the validation object

Private Sub Worksheet_Change(ByVal Target As Range)
  if target.address = activesheet.[A1]/address then
    'assume list B starts in AA1
    activesheet.[AA1] = Activesheet.[A1]-7
  end if
End Sub

List B in AA1 has this formula in AA2 and following
[tt]
=AA1+1
[/tt]


Skip,

_[red][/red]

 

[firefytr]

Why not use the native objects...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$A$1" Then
        Me.Range("AA1").Value = Target.Value - 7
    End If
End Sub

Just good practices IMHO.

Take care!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[inkserious]

Okay, so I simply enter the date in each cell and then I change the date in A1, AA1 automatically changes to A1 - 7. And yes, I can still change the date in AA1. All works as needed with the exception of: I need both A1 and AA1 to be a drop down menu with the dates of the month. When I try to use Data>Validation>List and reference the source against a range of dates, the code will not work.

Any ideas on how I can make these two cell a drop down menu?

Thanks all.

-ep

 

[SkipVought]

List B is in column AA. AA1 should be the first cell in the list.

Where is the List A?

Skip,

_[red][/red]

 

[inkserious]

Okay, I got List B working. I misread your post Skip. I had List B starting in AA instead of AA1.

List A is E2:E32.

...we're getting close. Thanks for all the help thus far.

-ep

 

[SkipVought]

If you want list A to change based on the selection in the validation object in A1, then you must do the same thing to List A as you do to List B using the desired logic.

Skip,

_[red][/red]

 

[inkserious]

I don't want List A to change, but rather I want A1 to be List A. As it is working now, whatever date is entered into A1 causes AA1 to default to A1 - 7. This is the functionality I need. I now just need A1 to also be a drop down list.

Thanks Skip..

-ep

 

[SkipVought]

I guess it's done, then.

Skip,

_[red][/red]

 

[inkserious]

I'm still trying revisit this issue. I've got List B funtioning exactly as I need. What I'm still unable to resolve is making A1 a list too. Currently, A1 is simply a date cell that List B references from. Is it possible to make List A a drop down list with the dates of the month also?

Thanks.

ep

 

[SkipVought]

Sure. Just do a Data/Validation - List in another cell.

Skip,

_[red][/red]