I put together a workbook that does what you want. Unfortunately I could not imagine a way to do it with worksheet lookup formulas so I had to write some code to do it.
The workbook is
HERE.
I hope that you have at least some familiarity with VBA code?
I did you a favor and removed merged cells. As kevin9999 said, they really are a pain to deal with! I used cell alignment called Center Across Selection instead. Right click on cells to be formatted then select Format Cells menu item => Alignment tab
As the kevin9999 suggested, I used range names. Excel allows you to give a meaningful name to a cell or range of cells. For example, one source data table is named Table2010D25. In fact, all data tables must have a name with the same general pattern as that or my code will not find them. In this case there was another table which is named Table2010D26. Similarly the cell where user specifies the source data table is named Table.
To apply a name for a source data table select all cells in the respective source data table, including the empty cell in the upper left of the data and including the numeric row and column headers, then use Formula => Name Manager menu item to apply names. Importantly I used names whose "scope" is for the worksheet. Names can be scoped to the workBOOK or workSHEET. You specify the scope -- Sheet1 in my workbook -- when creating the name.
The workbook uses the Worksheet_Change event for the worksheet containing the data tables. I'll leave it to you to read up on worksheet events. In short, worksheet events are "triggered" when something related to the worksheet occurs. The Worksheet_Change event triggers when there is any change to the worksheet. In your case we only want the event to do something -- get the data from the specified source data table -- when someone changes the cell named Table.
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' If user changed the cell name Table then copy the specified source
' data table and paste its values into the target data table. Do by
' calling sub named GetTableData.
If Target.Cells(1) = Me.Range("Table") _
Then
Call GetTableData(Me)
End If
End Sub
If user does change the cell named Table then the Worksheet_Change event calls the sub that gets the data from the specified source data table.
VBA Code:
Option Explicit
' ----------------------------------------------------------------
' Procedure Name: GetTableData
' Purpose: Put the specified source table data's date into the target data table.
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter pwsData (Worksheet): The worksheet object that points to the worksheet to process..
' Author: Jim
' Date: 6/6/2023
' ----------------------------------------------------------------
Sub GetTableData(pwsData As Worksheet)
' Name of the source data table.
Dim psSourceTableName As String
' Upperleftmost cell in the target data table (where data is placed).
Dim rTargetAnchorCell As Range
' The range is set to be the entire source data table including headers.
Dim rSourceTableRange As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With pwsData
' Set the anchor cell (upperleftmost cell) for the target data range.
' As of 6/5/23 there is no value in the anchor cell.
Set rTargetAnchorCell = .Range("TableAnchorCell")
' Get source the data table name using the two named ranges (Cells) in the
' worksheet: 1. Year and 2. Table. That is used to refer to the table.
psSourceTableName = "Table" & .Range("Year") & .Range("Table")
' Attempt to point the rSourceTableRange range object to the range
' whose name is in psSourceTableName -- the source data table.
On Error Resume Next
Set rSourceTableRange = .Range(psSourceTableName)
On Error GoTo 0
' If the attempt to set the range object above then tell user and exist sub.
If rSourceTableRange Is Nothing _
Then
MsgBox "The named range for Table " _
& .Range("Year") & " " & .Range("Table") _
& " does not exist.", vbCritical
Exit Sub
End If
' Copy the source data table then paste values only into the target range.
rSourceTableRange.Copy
rTargetAnchorCell.PasteSpecial Paste:=xlPasteValues
' Make cell rTargetAnchorCell the active cell.
rTargetAnchorCell.Select
Application.CutCopyMode = False
End With
Application.EnableEvents = True
End Sub
Consider using Data Validation to make a dropdown list in the cell named Table.
