Match Problem?

[chris_chen1028]

I've been trying to figure out this all day basically.

Please see attached file.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Game Type[/TD]
[TD]Day of Week[/TD]
[TD]Day Before[/TD]
[TD]Day Of[/TD]
[TD]Day After[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]Monday[/TD]
[TD]-1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]Tuesday[/TD]
[TD]-5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Baseball[/TD]
[TD]Tuesday[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Hockey[/TD]
[TD]Wednesday[/TD]
[TD]-1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

1) I need help creating a data validation list that selects ONLY the unique day of the week. (So no duplicate Tuesday here)

2) From that selection (Tuesday, for example), any day of week selection would return all attributed Game Types (so both Basketball and Baseball) and the columns "Day Before" to "Day After" (so -5, 5, 4 next to "Basketball" in a row as well as 10,5,4 next to Baseball on a separate row after selecting "Tuesday" from the data validation list).

Any help would be much appreciated.. thank you.

 

[Al Chara]

Why don't you just use AutoFilter?

 

[L. Howard]

Start with this, I did not see any file attached so this will need some range adjustments.

Where:
"DaysOf" is a named range of the Mon, Tue, Wed etc.
Range("C1") has a drop down Data Valadation with the days of the week in it, select a day, run the code, stuff goes to columns H, I, J, K.

Option Explicit
Sub DaysOf()
Dim c As Range
For Each c In Range("DaysOf")
  If c.Value = Range("C1").Value Then
    Range("H100").End(xlUp).Offset(1, 0) = c.Offset(0, -1)
     Range("I100").End(xlUp).Offset(1, 0) = c.Offset(0, 1)
      Range("J100").End(xlUp).Offset(1, 0) = c.Offset(0, 2)
       Range("K100").End(xlUp).Offset(1, 0) = c.Offset(0, 3)
  End If
Next
End Sub

Rgards,
Howard

 

[chris_chen1028]

Hi Howard - Thank you for the reply. Looks like I dont have permission to attach files yet.

The people that will be viewing this file won't be very keen on VBA... xlsm files are beyond what they will want.

I've been having trouble getting the fields to auto-populate since there are two fields that I want to meet (DOW, Game Type). I guess in other words, is there a way to use =MATCH to grab the data that meet those day of week AND Game Type selections?

Thanks...

Start with this, I did not see any file attached so this will need some range adjustments.

Where:
"DaysOf" is a named range of the Mon, Tue, Wed etc.
Range("C1") has a drop down Data Valadation with the days of the week in it, select a day, run the code, stuff goes to columns H, I, J, K.

Option Explicit
Sub DaysOf()
Dim c As Range
For Each c In Range("DaysOf")
  If c.Value = Range("C1").Value Then
    Range("H100").End(xlUp).Offset(1, 0) = c.Offset(0, -1)
     Range("I100").End(xlUp).Offset(1, 0) = c.Offset(0, 1)
      Range("J100").End(xlUp).Offset(1, 0) = c.Offset(0, 2)
       Range("K100").End(xlUp).Offset(1, 0) = c.Offset(0, 3)
  End If
Next
End Sub

Rgards,
Howard

 

[Al Chara]

If you are not keen on AutoFilter, then would an acceptable solution be to have data validation in one cell to show all unique days of the week; selecting a day on the week would populate another data validation list of all relevant Game Types; and selecting the Game Type would return the equivalent days before, on, and after?

 

[chris_chen1028]

If you are not keen on AutoFilter, then would an acceptable solution be to have data validation in one cell to show all unique days of the week; selecting a day on the week would populate another data validation list of all relevant Game Types; and selecting the Game Type would return the equivalent days before, on, and after?

Yes! Ideally I would like to just have one data validation list (for days) to return all relevant Game Types (and the equivalent before, on, after etc.). Suffice it to say, I'm having more than enough trouble with the index, match, and offset formulas. Thanks for all your help.

 

[L. Howard]

This might get going, it's a bit of a study. The multiple-days-of-the-week-lookups seems to be taken care of here.

hhtp://chandoo.org/wp/2010/vlookup-second-value/

(you will have to type this into your search window, I could not get it to carry the link attribute when I copied and pasted it here)

Once there, be sure to read the SITUATION.

Under SOLUTION note the formula =CS&COUNTIF($C$5:C%,C5).

Under SAMPLE FILE give a hard study of the example provided.

I "think" it is adaptable to your situation.

Good luck.

Regards,
Howard

 

[Al Chara]

You can take a look at the following link, where I explain how to set up dynamic dependent lists, which should be adaptable to your situation. Link to example file at the bottom of the page.

http://mrspreadsheets.com/1/post/2012/9/excel-function-tutorial-6-dynamic-dependent-lists.html

 

[chris_chen1028]

You can take a look at the following link, where I explain how to set up dynamic dependent lists, which should be adaptable to your situation. Link to example file at the bottom of the page.

Excel Function Tutorial #6 - Dynamic Dependent Lists - Mr Spreadsheets

This is great - I love the step by step process. Having a little trouble with the private sub function (Worksheet_SelectionChange).. it points to an error with the UpdateValidation Statement.

Also getting an error with the fifth step - the drop-down box:

My source data is between columns P and AB, and would like to pull data between columns A and M... So "P" would be the sorted Game_Type, Day of Week is in the column over ("Q").

For some reason, despite entering "Sheet1!A2," the "H1048574" takes the place of that.... what did I do wrong here?

=OFFSET(Sheet1!$P$1,MATCH(Sheet1!H1048574,Sheet1!$P$2:$P$25,0),1,COUNTIF(Sheet1!$P$2:$P$25,Sheet1!H1048574),1)

Have been struggling with this all day - your work - especially on Super Bowl weekend is much appreciated. Thanks.