Search multiple spreadsheets and return value

Ryan1uk

New Member
Joined
Jul 8, 2020
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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 :)
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Look down the column and if there is something in the cell return some text to Dashboard J2

Are you looking for only one value in column AB or could there be more than one value from AB9 and down? Also, you want to do this with all the sheets which means that you may have more than one value from the AB columns. You want to return the found values to cell J2 of Dashboard. Do you want all the found values in the one cell J2 ? Please clarify in detail.
 
Upvote 0
Hi Mumps, thank you very much for your reply. Sorry about that there will only be one value across all sheets.
 
Upvote 0
Try:
VBA Code:
Sub SearchSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet, rngVal As Range, bottomAB As Long
    Dim bottomA As Long
    Set desWS = Sheets("Dashboard")
    Set rngVal = desWS.Range("D2")
    For Each ws In Sheets
         If ws.Name <> "Dashboard" Then
            If ws.Range("B9") = rngVal Then
                With ws
                    If WorksheetFunction.CountA(.Range("AB9", .Range("AB" & .Rows.Count).End(xlUp))) > 0 Then
                        bottomAB = .Range("AB" & .Rows.Count).End(xlUp).Row
                        desWS.Range("J2") = .Range("AB" & bottomAB)
                        Exit Sub
                    End If
                End With
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
WOW was not expecting that :) But that does not seem to work :(

Also, I need it to do all rows in the column on Dashboard E.g D2, D3, D4, D5 etc and the same with J2, J3, J4 etc.

Did not put that in the initial post as thought I could just copy the excel formula down the column.

Just been doing some more looking and will I need to use VLOOKUP?
 
Upvote 0
So you want to search for all the values in column D and the results should be placed in column J starting at J2. Is that correct?
 
Upvote 0
Just wanted to add the script worked perfectly I never ran it :eek: Like I say I need it for Dashboard E.g D2, D3, D4, D5 etc and the same with J2, J3, J4 etc.
 
Upvote 0
See if this does what you want.
VBA Code:
Sub SearchSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet, bottomAB As Long, rng As Range
    Dim bottomA As Long
    Set desWS = Sheets("Dashboard")
    Set rngVal = desWS.Range("D2")
    With desWS
        For Each rng In .Range("D2", .Range("D" & .Rows.Count).End(xlUp))
            For Each ws In Sheets
                 If ws.Name <> "Dashboard" Then
                    If ws.Range("B9") = rng Then
                        With ws
                            If WorksheetFunction.CountA(.Range("AB9", .Range("AB" & .Rows.Count).End(xlUp))) > 0 Then
                                bottomAB = .Range("AB" & .Rows.Count).End(xlUp).Row
                                desWS.Cells(desWS.Rows.Count, "J").End(xlUp).Offset(1) = .Range("AB" & bottomAB)
                                Exit For
                            End If
                        End With
                    End If
                End If
            Next ws
        Next rng
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks again for your time.

I tested it with 5 values on different rows and different sheets and it returns the first one but then returns the second about 10 times then stops. Also, it doesn't return them on the right row Eg Dashboard J2, J3 J4 etc it returns them further down the page.

Hope I have explained that well enough as you can tell not very good at that.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,697
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top