Fetching data in various cells based on on one cell

[kvinay_00]

Hello,

I am trying to fetch data from a data base in various cells based on another sheet based a cell value. i have tried vlookup/ index/ match etc but unable to get the right formula. Sample file i attached for reference.

Can anyone help please?

Thanks in advance!

[SamT]

I don't think formulas can handle a table with duplicate lookup Keys

Save As the workbook as an .xlsm file, than copy these two codes as indicated

In the CodePage for the Data sheet
Option Explicit

Private CountryStartCell As Range
Private CountryEndCell As Range

Function CountryData(Country As String) As Range
SetCells Country
Set CountryData = Range(CountryStartCell, CountryEndCell)
End Function

Private Function SetCells(Country As String)
Set CountryStartCell = Range("A:A").Find(What:=Country, lookat:=xlWhole, After:=Range("A1"), SearchDirection:=xlNext)
Set CountryEndCell = Range("A:A").Find(What:=Country, lookat:=xlWhole, After:=Range("A1"), SearchDirection:=xlPrevious).End(xlToRight)
End Function


In the CodePpage for the Table Sheet
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then GetData Target
End Sub



Private Sub GetData(ByVal Country As String)

Application.EnableEvents = False

ClearTable
Sheet1.CountryData(Country).Copy Range("A4")

Application.EnableEvents = True
End Sub


Private Sub ClearTable()
Dim LastCell As Range

With Range("A4").CurrentRegion
Set LastCell = .Cells(.Cells.Count)
End With

Range(Range("A4"), LastCell).ClearContents

End Sub

[kvinay_00]

Thank you so much for your help!