Search multiple spreadsheets and return value

[Ryan1uk]

Hi,

Hope you are all having a lovely day and staying safe. I recently downloaded a template spreadsheet and wanted to change it a little. I have only started learning Excel so please excuse me, I would have searched but don't really know what to search for and if it's even possible.

I have 11 spreadsheets open in a workbook.

Dashboard, Name, Name (2), Name (3) etc

• First I need to get the name from Dashboard D2
• Then search across the 10 spreadsheets for that name which will appear in Name B9, Name (2) B9, Name (3) B9 etc
• Go across the column to Name AB9, Name (2) AB9, Name (3) AB9 etc
• Look down the column and if there is something in the cell return some text to Dashboard J2

Any help is much appreciated and have a good day.

Ryan

 

[mumps]

That's because only one sheet (Bet Angel) has a values in B9.

 

[Ryan1uk]

I see but what about Havana Sunset D2 it has a value in Bet Angel AB12 but that is not returning anything in J3?

 

[mumps]

Then search across the 10 spreadsheets for that name which will appear in Name B9, Name (2) B9, Name (3) B9 etc

In your original post, you mentioned to check only cell B9. Are you saying that the name can be anywhere in column B?
There is another serious problem with the fact that you have merged cells in column B. Have a look at the Bet Angel sheet. If you click on the name "Rosa Gold" you will see that in the formula bar, it shows as being in cell B9. You will also note that B9 and B10 are merged. If you go to column AB and click on the "1" for Rosa Gold, you will see that the the number "1" is shown to be in cell AB10. So the name is not in the same row as the number. The two different rows will confuse the macro. You should avoid using merged cells at all cost because they almost always cause problems for macros. You also have merged cells in columns T to AE. I would suggest that you remove all the merged cells in all the sheets. This will take some time but it will ensure that the macros will work properly. Do a little research into "CenterAcrossSelection". It has the same visual effect as merging the cells without actually merging them. When you have done this, 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. If the workbook contains confidential information, you could replace it with generic data.

 

[Ryan1uk]

Sorry, the reason why I said that was because I thought there was some formula I could put in J2 then copy it to J3 etc. I was thinking of VLOOKUP but not sure how to search across multiple sheets.

I tried VLOOKUP(D2,'Bet Angel'!B8:AB68,20,FALSE) to see if it works with one-sheet but can not even get that to work.

 

[mumps]

No problem. Will you be able to make the changes I suggested?

 

[Ryan1uk]

Afraid not the program I use puts values in them cells, so I do not want to change anything.

Really appreciate your time and you trying to help me

 

[mumps]

OK. When I have a little time, I will revisit your file to see if I can find a work-around and I'll let you know.

 

[Ryan1uk]

If you're ever in Norwich, England I owe you a pint

 

[mumps]

Thanks for the offer but with this ugly virus hanging around, I don't think that I'll be travelling any time soon. ?

Try this version of the macro. Fortunately, I don't think that the merged cells affect it. I noticed that Cortan Lad is spelled differently on the two sheets. Please fix this issue or the name won't be recognized by the macro.

Sub SearchSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet, bottomJ As Long, rng As Range, firstRow As Long, lastRow As Long, fnd As Range
    Set desWS = Sheets("Dashboard")
    With desWS
        lastRow = .Cells(Cells(1, "A").End(xlDown).Row, "A").Row
        .Range("J2:J" & lastRow).ClearContents
        bottomJ = .Range("J1:J" & lastRow).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    End With
    With desWS
        For Each rng In .Range("D2:D" & lastRow)
            For Each ws In Sheets
                 If ws.Name <> "Dashboard" Then
                    Set fnd = ws.Range("B:B").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        desWS.Cells(bottomJ, 10) = ws.Range("AB" & fnd.Row + 1)
                        bottomJ = desWS.Range("J1:J" & lastRow).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                        Exit For
                    End If
                End If
            Next ws
        Next rng
    End With
    Application.ScreenUpdating = True
End Sub

 

[Ryan1uk]

That works to some extent. It returns all the values into the J column but not into the correct row. EG I added on Bet Angel (2) Dujac instead of returning the value to J53 it returned it to J9.