Search multiple spreadsheets and return value

Ryan1uk

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
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.
 

Ryan1uk

New Member
Joined
Jul 8, 2020
Messages
17
Office Version
2019
Platform
Windows
Hi Mumps, thank you very much for your reply. Sorry about that there will only be one value across all sheets.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
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
 

Ryan1uk

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
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?
 

Ryan1uk

New Member
Joined
Jul 8, 2020
Messages
17
Office Version
2019
Platform
Windows
Yes :)
 

Ryan1uk

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,154
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
 

Ryan1uk

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

Watch MrExcel Video

Forum statistics

Threads
1,102,120
Messages
5,484,852
Members
407,470
Latest member
SusanJohnst

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top