lookup valuest at multiple sheets

[Zoli]

Hello all.

I new here so I hope I write my question at right place. I need your help.

I have a workbook with multiple sheets and I create one sheet called DATA+sheetlist of all sheets at workbook.
At all sheets In column B3:B1000 I have order numbers at columns C3:C1000 company names and at columns R3:R1000 values.
At DATA sheet at column B3:B1000 I create order numbers + company names which I need (order number&" "&company name) as example: 2002514 CCLA-PBC-S5F
So what exactly I need is to return the sheet name at DATA sheet at columns C3:C1000. So lookup value (cell B3 till B1000 at DATA sheet) check columns B and C at all sheets at workbook, if match check column R and if this value is bigger than 0 he will return the sheet name.

Is it possible with functions?

thank you guys

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your DATA sheet and one or two of the other sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[KRice]

Let us know if this is close to what you want to do. The list of worksheets present in your workbook is shown in column G of the DATA sheet. It sounds as if you have concatenated Order Numbers and Company Names in column B of the DATA sheet. I am assuming that the first space appears between the Order Number and the Company Name (something is needed to understand how to split the text string). Sample worksheets showing Order Numbers and Company Names and Values are shown further below.

MrExcel20210215.xlsx
	B	C	D	E	F	G
1	order numbers have no spaces
2	Order # + Company Name	Values				sheetlist
3	ccd-324 hotel	302				Sheet 1
4	bcd-225 foxtrot	203				Sheet 2
5	abc-124 bravo	102				Sheet 3
DATA

Cell Formulas
Range	Formula
C3:C5	C3	=IFERROR(VLOOKUP(LEFT($B3,FIND(" ",$B3)-1), INDIRECT("'"&INDEX($G$3:$G$5, MATCH(TRUE, COUNTIFS( INDIRECT("'"&$G$3:$G$5&"'!B3:B100"), LEFT($B3,FIND(" ",$B3)-1), INDIRECT("'"&$G$3:$G$5&"'!C3:C100"), RIGHT($B3,LEN($B3)-FIND(" ",$B3)) )>0,0)) & "'!B3:R100"),17,0),"not found")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

MrExcel20210215.xlsx
	B	C															R
1	Order Numbers	Company Names															Values
2	abc-123	alpha															101
3	abc-124	bravo															102
4	abc-125	charlie															103
Sheet 1

MrExcel20210215.xlsx
	B	C															R
1	Order Numbers	Company Names															Values
2	bcd-223	delta															201
3	bcd-224	echo															202
4	bcd-225	foxtrot															203
Sheet 2

MrExcel20210215.xlsx
	B	C															R
1	Order Numbers	Company Names															Values
2	ccd-323	golf															301
3	ccd-324	hotel															302
4	ccd-325	india															303
5	ccd-326	juliet															304
Sheet 3

 

[Zoli]

Hi everybody

Thanks for you reply but this is not what I exactly need. So I make a small example and upload file to dropbox. Below is the link:

Nová položka Microsoft Excel Worksheet.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

Try:

Sub GetSheetName()
    Application.ScreenUpdating = False
    Dim pName As Range, fnd As Range, desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Data")
    With desWS
        For Each pName In .Range("C4", .Range("C" & .Rows.Count).End(xlUp))
            For Each ws In Sheets
                If ws.Name <> "Data" Then
                    Set fnd = ws.Range("C:C").Find(pName, LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        If fnd.Offset(, 7) > 0 Then
                            pName.Offset(, 1) = ws.Name
                            Exit For
                        End If
                    End If
                End If
            Next ws
        Next pName
    End With
    Application.ScreenUpdating = True
End Sub

 

[Zoli]

Thank you mumps.

Is it possible with functions?

 

[mumps]

You are very welcome. If by "functions" you mean formulas, I don't think it is possible.