Solved How to return single value searching multiple sheets?

January 15, 2016 at 04:15:48
Specs: Windows 7
Hi, really hope you can help me with a tricky (I assume) VLOOKUP problem in Excel 2010,

I have a spreadsheet provided by a colleague which lists asset information over a number of similar sheets. Each sheet is a room and the sheet lists up to 60 assets along with associated asset data. Each row is a unique entity/asset across the whole spreadsheet. On all these sheets the unique identifier is in Row A and I need to return the value in row W (asset life).

I have added a MySheets sheet which lists the sheet names.

I have added a master sheet which lists all entities (taken from another asset list) but has older data (which I need to update). I've added a blank column G next to the existing data that I want to compare.

For each of the 7000 rows in the Master sheet I need to search for the same unique item in column B throughout all the other sheets (column A). I then need to return the value in column W from whatever sheet it is on into column G of the correct asset in the Master Sheeet.

Sorry for the long winded explanation, hopefully it makes some sort of sense. I've tried various versions of VLOOKUP and INDEX (found after google searches) but can't work out the correct syntax. With luck one of you much cleverer individuals may be kind enough to assist.

Many thanks in advance.

message edited by GaryG

See More: How to return single value searching multiple sheets?

Report •

January 15, 2016 at 07:47:52
It can be done, but it's a bit complicated, this page has
a good tutorial on how to get it to work:

see if it does what your looking to do.


Report •

January 15, 2016 at 08:54:52
✔ Best Answer
Perhaps this User Defined Function (UDF) will do what you want:

Function Unique(rng As Range)
Dim shts As Long
Dim u As Range
Dim tmpVal
'Loop through sheets looking for value
  For shts = 1 To Sheets.Count
'Skip Master Sheet
     If Sheets(shts).Name <> "Master Sheet" Then
'Search for value from master list
       With Sheets(shts).Columns(2)
        Set u = .Find(rng, lookat:=xlWhole)
'If value is found, grab value from column W
          If Not u Is Nothing Then
            tmpVal = Sheets(shts).Cells(u.Row, "W")
            Exit For
          End If
       End With
     End If
'Place value in cell with Function
    Unique = tmpVal
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question