How Can I Reference multiple sheets

[camshaftss]

Looking for some help to reference multiple sheets. I have a master spread sheet for parts used and multiple sheets of equipment each parts are used. I would like to display on the master sheet where each of the parts are used.

so in the master sheet parts are listed in column D and I'd like to in column X that part # in D15 is used on MCC 322(name of other sheet where the same part number is found) pulled from the 322 MCC Sheet Cell D128. Id like to do this for each part number on the master sheet "nonduplicated" and from multiple sheets if possible. if its simpler/easier to have additional columns for each sheet referenced id be happy with that

 

[sxhall]

Hi Camshaftss!

I would go with the additional columns to look it up, one for each sheet. Simpler to add in another column and tab at a later if ever required.

Mocked up the below and you would have the columns in grey for each sheet, which could be hidden, and then Column H is a count of matches found and column I is the tab/s it is found on.

Formulas I've used are as follows...

E2:E5...

=IF(COUNTIF(Sheet2!$D:$D,"=" &$D2)>0,$E$1,"")

F2:F5

=IF(COUNTIF(Sheet3!$D:$D,"=" &$D2)>0,$F$1,"")

G2:G5...

=IF(COUNTIF(Sheet4!$D:$D,"=" &$D2)>0,$G$1,"")

Cells E1:G1 need to have the tab names in them. This to return in the sheet name it is looking up against in the formulas above.

H2:H5...

=COUNTIF(E2:G2,"=" & "?*")

I2:I5...

=TEXTJOIN(", ",TRUE,FILTER(E2:G2,E2:G2<>"","Not Found"))

By adding the count it will show if more than 1 sheet has a match found, and any sheet names will be added in to column 'I'. In my example I have part DEF on 2 sheets and getthe return for both. Part JKL is on none so get 'Not Found' as a result.

 

[camshaftss]

Hi Camshaftss!

I would go with the additional columns to look it up, one for each sheet. Simpler to add in another column and tab at a later if ever required.

Mocked up the below and you would have the columns in grey for each sheet, which could be hidden, and then Column H is a count of matches found and column I is the tab/s it is found on.

Formulas I've used are as follows...

E2:E5...

=IF(COUNTIF(Sheet2!$D:$D,"=" &$D2)>0,$E$1,"")

F2:F5

=IF(COUNTIF(Sheet3!$D:$D,"=" &$D2)>0,$F$1,"")

G2:G5...

=IF(COUNTIF(Sheet4!$D:$D,"=" &$D2)>0,$G$1,"")

Cells E1:G1 need to have the tab names in them. This to return in the sheet name it is looking up against in the formulas above.

H2:H5...

=COUNTIF(E2:G2,"=" & "?*")

I2:I5...

=TEXTJOIN(", ",TRUE,FILTER(E2:G2,E2:G2<>"","Not Found"))

By adding the count it will show if more than 1 sheet has a match found, and any sheet names will be added in to column 'I'. In my example I have part DEF on 2 sheets and getthe return for both. Part JKL is on none so get 'Not Found' as a result.

Hi Camshaftss!

I would go with the additional columns to look it up, one for each sheet. Simpler to add in another column and tab at a later if ever required.

Mocked up the below and you would have the columns in grey for each sheet, which could be hidden, and then Column H is a count of matches found and column I is the tab/s it is found on.

Formulas I've used are as follows...

E2:E5...

=IF(COUNTIF(Sheet2!$D:$D,"=" &$D2)>0,$E$1,"")

F2:F5

=IF(COUNTIF(Sheet3!$D:$D,"=" &$D2)>0,$F$1,"")

G2:G5...

=IF(COUNTIF(Sheet4!$D:$D,"=" &$D2)>0,$G$1,"")

Cells E1:G1 need to have the tab names in them. This to return in the sheet name it is looking up against in the formulas above.

H2:H5...

=COUNTIF(E2:G2,"=" & "?*")

I2:I5...

=TEXTJOIN(", ",TRUE,FILTER(E2:G2,E2:G2<>"","Not Found"))

By adding the count it will show if more than 1 sheet has a match found, and any sheet names will be added in to column 'I'. In my example I have part DEF on 2 sheets and getthe return for both. Part JKL is on none so get 'Not Found' as a result.

View attachment 120468

Thanks , This seems to work for the most part but I am having trouble with it not pulling all the data say on sheet 3 has 157 part numbers but its only pulling about 12 of the numbers. I thought maybe it was that they were just entered slightly differently so i copied the cell from sheet on onto sheet 3 so they would be the same but that didn't help. What am I missing???

 

[jolivanes]

Don't know how long looping will take.
Check and change references where required.

Sub Like_So_Maybe()
Dim shM As Worksheet, ws As Worksheet
Dim i As Long, lr As Long
Dim shName
Set shM = Worksheets("Sheet1")    '<---- Change as required
lr = shM.Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To lr
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> shM.Name Then
                If Application.WorksheetFunction.CountIf(ws.Columns(4), shM.Cells(i, 4).Value) > 0 Then
                    shName = shName & "|" & ws.Name
                End If
            End If
        Next ws
    shName = Join(Split(Mid(shName, 2), "|"), ", ")
    shM.Cells(i, 24).Value = shName
    shName = ""
    Next i
End Sub