Creating weekly matchup calendar for NBA

[dudeman123]

Hi everyone, thank you in advance for taking the time to read my post. I will keep my post as brief as possible. I am trying to create a weekly calendar for the NBA season, such that I can visualize matchup density and home/away splits. I currently have the complete matchup calendar in a vertical format (left side of image), and I would like the calendar to be the opposite of that where date is represented horizontally (x) -- (right side of image). When I put new dates in the 7-day calendar window I'd like to automate the population of matchups there. Ideally, I'd like to determine a basic formula (i.e. a formula that doesn't have a long buffer time to complete when I make adjustments) and that doesn't use VBA/macros (if that is possible).

Any help you might be able to offer would be greatly appreciated!

 

[OaklandJim]

Consider posting a segment of the worksheet using MrExcel's excellent add in name XL2BB rather than the picture. Do it so we can have data involved.

See here for more: XL2BB - Excel Range to BBCode

Better yet, because the task is not totally clear -- to me at least -- post the workbook as a link.

That said, without a better understanding of what you want it may be that VBA is indicated. Is there a reason that you don't want VBA? A fast macro could probably populate cells located on the right, based on cell values on the left.

On the other hand, it may be doable with formulas.

I think I understand what you want. I'll pick an example, in the column for 10/19. ATL has HOU, BKN has NOL, CHA has @SAS, CHI has @MIA, etc.?

When I put new dates in the 7-day calendar window

Where/What is that? Do you enter seven new dates on the right side?

 

[dudeman123]

Consider posting a segment of the worksheet using MrExcel's excellent add in name XL2BB rather than the picture. Do it so we can have data involved.

See here for more: XL2BB - Excel Range to BBCode

Better yet, because the task is not totally clear -- to me at least -- post the workbook as a link.

That said, without a better understanding of what you want it may be that VBA is indicated. Is there a reason that you don't want VBA? A fast macro could probably populate cells located on the right, based on cell values on the left.

On the other hand, it may be doable with formulas.

I think I understand what you want. I'll pick an example, in the column for 10/19. ATL has HOU, BKN has NOL, CHA has @SAS, CHI has @MIA, etc.?


Where/What is that? Do you enter seven new dates on the right side?

I'm actually okay with VBA if that's the best route to go. The only reason I suggested not to use VBA is because I wasn't sure if it would impact my next step, which is to conditional format matchups in the calendar array based on opponent strength, etc.

I'm not sure how to link my workbook to here (I remember years ago when I came here I thought I could just straight-up attach my document, oh well). Do I need to start a whole new thread to use the XL2BB? I saw it on my initial post but now I'm not seeing it while I'm replying here.

Your assumption is correct and the example is accurate in terms of what I would like to see in the column for 10/19.

And to answer your second question, yes so I will update the 7-day window as the season progresses to show the current week's matchups.

 

[OaklandJim]

I put together a VBA function that seems to do what you need. Unfortunately my code tends to be pretty klunky. One good thing about this list is that when I post MY code invariably some else comes up with a better solution, like a worksheet formula or tighter code for a VBA solution.

You'll need to use some of what I think is relative cell referencing. In your example data, in cell AB 2 is this formula =TeamDate($AA2, AB$1, $P$1). Copy that into all relevant cells. You can see what the function (below) does by looking at the sheet segment. If you hover over a cell the formula is shown.

Good luck and go Warriors! (I live in the Bat Area.)

AutoUpdateCellValue.xlsm
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Date	ATL	BOS	BKN	CHA	CHI	CLE	DAL	DEN			Team	10/17/2022	10/18/2022	10/19/2022	10/20/2022	10/21/2022	10/22/2022	10/23/2022
2	10/17/2022											ATL			HOU		ORL		CHA
3	10/18/2022		PHI									BOS		PHI			@MIA	@ORL
4	10/19/2022	HOU		NOP	@SAS	@MIA	@TOR	@PHX	@UTAH			BKN			NOP		TOR
5	10/20/2022											CHA			@SAS		NOP		@ATL
6	10/21/2022	ORL	@MIA	TOR	NOP	@WAS			@GSW			CHI			@MIA		@WAS	CLE
7	10/22/2022		@ORL			CLE	@CHI	MEM	OKC			CLE			@TOR			@CHI	WAS
8	10/23/2022	CHA			@ATL		WAS					DAL			@PHX			MEM
9	10/24/2022		@CHI	@MEM		BOS			@POR			DEN			@UTAH		@GSW	OKC
Sheet1

Cell Formulas
Range	Formula
AB2:AH9	AB2	=TeamDate($AA2, AB$1, $P$1)

Named Ranges
Name	Refers To	Cells
Header_ScheduleDates	=Sheet1!$P$1	AB2:AH9

Function TeamDate(prTeam, prDate, prHeaderDate) As String

'   The worksheet with the data in it.
    Dim wsSchedule As Worksheet
    
'   Where team names for lookup are located.
    Dim rTeamsHorizontal As Range
    
'   Whre dates for lookup are located.
    Dim rDatesVertical As Range
    
'   Cell in the rTeamsHorizontal where the team name was found.
    Dim rTeam As Range
    
'   Cell in the rDatesVertical where the date name was found.
    Dim rDate As Range
    
'   Used for iterating through ranges rTeamsHorizontal & rDatesVertical
    Dim rCell As Range
       
'   Used to find the last cell in the column and in the row.
    Dim iLastCell As Long
    
'   The column of the target cell.
    Dim iOffsetColumn As Long
    
'   Set the worksheet object to point to the Parent of the parameter.
    Set wsSchedule = prTeam.Parent
    
'   Get the last occupied columnar cell for the range rTeamsHorizontal.
    iLastCell = prHeaderDate.End(xlToRight).Column

'   Set the range object that points to the teams listed horizontally.
    Set rTeamsHorizontal = prHeaderDate.Offset(, 1).Resize(1, iLastCell - prHeaderDate.Column)

'   Get the last occupied row cell for the range rDatesVertical.
    iLastCell = prHeaderDate.Offset(100000).End(xlDown).Row

'   Set the range object that points to the dates listed vertically.
    Set rDatesVertical = prHeaderDate.Offset(1).Resize(iLastCell - prHeaderDate.Row, 1)

'   Iterate through range rTeamsHorizontal looking for parameter psTeam.
    For Each rCell In rTeamsHorizontal
        If rCell.Value = prTeam.Value _
         Then
            Set rTeam = rCell
            Exit For
        End If
    
    Next rCell

'   Get column offset from the date column, where the value is, based on the
'   column in which the team was located.
    iOffsetColumn = rTeam.Column - wsSchedule.Range("Header_ScheduleDates").Column

'   Iterate through range rDatesHorizontal looking for parameter pdDate.
    For Each rCell In rDatesVertical
        If rCell.Value = prDate.Value _
         Then
            Set rDate = rCell
            Exit For
        End If
    
    Next rCell

'   The cell with the team is offset from date cell by iOffsetColumn columns.
'   Return the lookup value to the cell.
    TeamDate = rDate.Offset(0, iOffsetColumn).Value

End Function

 

[dudeman123]

I put together a VBA function that seems to do what you need. Unfortunately my code tends to be pretty klunky. One good thing about this list is that when I post MY code invariably some else comes up with a better solution, like a worksheet formula or tighter code for a VBA solution.

You'll need to use some of what I think is relative cell referencing. In your example data, in cell AB 2 is this formula =TeamDate($AA2, AB$1, $P$1). Copy that into all relevant cells. You can see what the function (below) does by looking at the sheet segment. If you hover over a cell the formula is shown.

Good luck and go Warriors! (I live in the Bat Area.)

AutoUpdateCellValue.xlsm
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Date	ATL	BOS	BKN	CHA	CHI	CLE	DAL	DEN			Team	10/17/2022	10/18/2022	10/19/2022	10/20/2022	10/21/2022	10/22/2022	10/23/2022
2	10/17/2022											ATL			HOU		ORL		CHA
3	10/18/2022		PHI									BOS		PHI			@MIA	@ORL
4	10/19/2022	HOU		NOP	@SAS	@MIA	@TOR	@PHX	@UTAH			BKN			NOP		TOR
5	10/20/2022											CHA			@SAS		NOP		@ATL
6	10/21/2022	ORL	@MIA	TOR	NOP	@WAS			@GSW			CHI			@MIA		@WAS	CLE
7	10/22/2022		@ORL			CLE	@CHI	MEM	OKC			CLE			@TOR			@CHI	WAS
8	10/23/2022	CHA			@ATL		WAS					DAL			@PHX			MEM
9	10/24/2022		@CHI	@MEM		BOS			@POR			DEN			@UTAH		@GSW	OKC
Sheet1

Cell Formulas
Range	Formula
AB2:AH9	AB2	=TeamDate($AA2, AB$1, $P$1)

Named Ranges
Name	Refers To	Cells
Header_ScheduleDates	=Sheet1!$P$1	AB2:AH9

Function TeamDate(prTeam, prDate, prHeaderDate) As String

'   The worksheet with the data in it.
    Dim wsSchedule As Worksheet
   
'   Where team names for lookup are located.
    Dim rTeamsHorizontal As Range
   
'   Whre dates for lookup are located.
    Dim rDatesVertical As Range
   
'   Cell in the rTeamsHorizontal where the team name was found.
    Dim rTeam As Range
   
'   Cell in the rDatesVertical where the date name was found.
    Dim rDate As Range
   
'   Used for iterating through ranges rTeamsHorizontal & rDatesVertical
    Dim rCell As Range
      
'   Used to find the last cell in the column and in the row.
    Dim iLastCell As Long
   
'   The column of the target cell.
    Dim iOffsetColumn As Long
   
'   Set the worksheet object to point to the Parent of the parameter.
    Set wsSchedule = prTeam.Parent
   
'   Get the last occupied columnar cell for the range rTeamsHorizontal.
    iLastCell = prHeaderDate.End(xlToRight).Column

'   Set the range object that points to the teams listed horizontally.
    Set rTeamsHorizontal = prHeaderDate.Offset(, 1).Resize(1, iLastCell - prHeaderDate.Column)

'   Get the last occupied row cell for the range rDatesVertical.
    iLastCell = prHeaderDate.Offset(100000).End(xlDown).Row

'   Set the range object that points to the dates listed vertically.
    Set rDatesVertical = prHeaderDate.Offset(1).Resize(iLastCell - prHeaderDate.Row, 1)

'   Iterate through range rTeamsHorizontal looking for parameter psTeam.
    For Each rCell In rTeamsHorizontal
        If rCell.Value = prTeam.Value _
         Then
            Set rTeam = rCell
            Exit For
        End If
   
    Next rCell

'   Get column offset from the date column, where the value is, based on the
'   column in which the team was located.
    iOffsetColumn = rTeam.Column - wsSchedule.Range("Header_ScheduleDates").Column

'   Iterate through range rDatesHorizontal looking for parameter pdDate.
    For Each rCell In rDatesVertical
        If rCell.Value = prDate.Value _
         Then
            Set rDate = rCell
            Exit For
        End If
   
    Next rCell

'   The cell with the team is offset from date cell by iOffsetColumn columns.
'   Return the lookup value to the cell.
    TeamDate = rDate.Offset(0, iOffsetColumn).Value

End Function

Thank you for your help I will give this a go!!

 

[OaklandJim]

Did the function I provided do what you needed? Can't wait for the season to start!