Pull data from different sheets based on one single value input

[anuwers]

Dear Friend.

I have excel sheet three or more worksheets.
.
1st sheet is the Master sheet - Where i will enter Key data and get required information in Master Sheet from 2nd sheet and 3rd sheet information.
My key will be the reference number.
It will be available in anywhere in the columns of 1st sheet and 2nd sheet.(Not necessary it should be only in reference column).
Even the reference can be available in between the text messages.
.
So, when i enter reference it should bring data from remaining two sheets and display if available.
Note for displaying in master sheet: if there is , more than one result it should appear one by one below.
.
2nd sheet has following columns
Reference number
Date
Transaction Amount
Name
Country
Related References

3rd sheet has following columns
Reference number
Related reference number
Text message 1
Text message 2

I have given sample for your review. Please support the same.

 

[Basavaraj K H]

Maybe your searching the same, pls refer attachment.

 

[anuwers]

Dear Basavaraj,

Excellent. Thanks for your respsonse. It is working with direct references in cell

I have extra question within that.

If you see in 3rd sheet - my reference number ANU00001 will be in between of the Text message 1 (2 column)
That is not picked, when i call the number in Master sheet.

Note: Basically - When i call the number in Master -
Query should search Reference column in Sheet 2 & then on sheet 3 it should search full sheet irrespective of columns ( if can be between) the text.

Can you assist with Macro codes as i will be having lot of data in my excel sheet.

Thanks

 

[Marc L]

Hi,​

according to the initial attachment a demonstration just using Excel basics so at beginner level where​

• headers names must be unique in a sheet​

• the code must be pasted to the Master worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address <> "$A$1" Then Exit Sub
    With Application
        .EnableEvents = False:  .ScreenUpdating = False
    With Me.UsedRange.Rows
        If .Count > 2 Then .Item("3:" & .Count).Clear
    End With
    If Target.Text > "" Then
        For N& = 2 To Worksheets.Count
            With Worksheets(N).[A1].CurrentRegion.Columns
                .Cells(2, .Count + 2).Formula = "=MATCH(""*" & Target.Text & "*""," & .Rows(2).Address(False, False) & ",0)"
                .AdvancedFilter xlFilterCopy, .Cells(1, .Count + 2).Resize(2), Cells(Rows.Count, 1).End(xlUp)(4)
                .Cells(2, .Count + 2).Clear
            End With
        Next
    End If
        .EnableEvents = True:  .ScreenUpdating = True
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[p45cal]

Try the attached.

Uses vlookup on 2NDSHEET and advanced filter, criteria as cells with formulae in protected cells on sheet 3RDSHEET.

Two small macros:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then Macro1
End Sub

Sub Macro1()
With Sheets("3RDSHEET")
  Set SceData = .UsedRange.Resize(, 4)
  Set rngCrit = .Range("F1:I5")
End With
SceData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=Sheets("Master").Range("A7:D7"), Unique:=False
End Sub

 

[anuwers]

Dear Marc,

Thanks it is perfectly working fine. Can I get the result in column wise instead of row wise.

Please start the column from 20th row. and use B column for sheet2 result, then use D column for sheet3 result.

Thanks

 

[Marc L]

This is not what you asked for in your initial post neither within your attachment and as it's unclear, at least attach an expected result workbook …​

​

 

[anuwers]

Dear Marc,

Sure. I will attach the expected result work book.
.
On the sheet 3 - on the column text message 1 and next column... if i have the value with numeric, results are not pulling out.
.
In other cells, related results are pulled out.

Please advise.

 

[Marc L]

Even the reference can be available in between the text messages.

As you clearly asked for searching within text so how can it be numeric ?!​

So according to your attachment I have no idea what you're talking about …​

​

 

[anuwers]

Dear Marc,

Sorry. I want to say that my search might be with only Numeric (or) only text (or) alphanumeric.
I have attached excel for more clarity.

Note: Above VBA exactly works, only i need to search additionally as said above.

 

[Marc L]

Read again post #4 - but well this time - as your new attachment has exactly the same problem​

as it does not respect the easy direction warning about headers !​

​

On my side no issue with the post #4 code as it is with worksheets respecting at least how Excel works …​

​

 

[p45cal]

In the attached, changes to my previous offering are:
On the Master sheet:

• Cell A1 formatted as Text
• Formulae changed in cells A5:F5 (Note that there could be many matches if the search item in cell A1 is short: only the first found is displayed)
• Validation in cell A1 changed to include partial match (but you should delete it anyway)

On the 3RDSHEET sheet:

• Formulae added to protected cells K1:L1

 

[anuwers]

Hi,​

according to the initial attachment a demonstration just using Excel basics so at beginner level where​

• headers names must be unique in a sheet​

• the code must be pasted to the Master worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address <> "$A$1" Then Exit Sub
    With Application
        .EnableEvents = False:  .ScreenUpdating = False
    With Me.UsedRange.Rows
        If .Count > 2 Then .Item("3:" & .Count).Clear
    End With
    If Target.Text > "" Then
        For N& = 2 To Worksheets.Count
            With Worksheets(N).[A1].CurrentRegion.Columns
                .Cells(2, .Count + 2).Formula = "=MATCH(""*" & Target.Text & "*""," & .Rows(2).Address(False, False) & ",0)"
                .AdvancedFilter xlFilterCopy, .Cells(1, .Count + 2).Resize(2), Cells(Rows.Count, 1).End(xlUp)(4)
                .Cells(2, .Count + 2).Clear
            End With
        Next
    End If
        .EnableEvents = True:  .ScreenUpdating = True
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

If i want to get the results from 25th row..
please provide the updated code.