VLOOKUP with multple row returns

  • Thread starter Thread starter penc
  • Start date Start date
P

penc

I have a workbook with supporting worksheets. On the
first worksheet I list general information about sites
including a site number. On the second worksheet I've got
a four column, 1599 row data set who's first column is the
site number followed by specific sub organization
information. The site number is the key inforamtion.
What I'm trying to do is have the first worksheet linked
to the second by a search for the LIKE site numbers held
in the second worksheet - and return into view all the
rows that meet that site number search. You'd click on
the first sheet's site number box and then all the sub
sites under that site number would come up.

I'm questioning the return of multiple rows and wondering
if a pivot table would be more appropriate although I'm
not looking to return sums and totals.
 
Not sure there is a formula that can accomplish this. However, Data|Group
and Outline would enable you to hide/unhide the sub-organization information
for each site. When you click on the +-. The details would have to be in
the same worksheet with the Summary Information.

PC
 
Hi

You need a third sheet!

Define site numbers column in general information table as named range p.e.
Sites.

At top of sheet format some cell, p.e. A1 with Data Validation as
list=Sites, and get rest of general information from Sheet1 into adjacent
cells, using VLOOKUP(). When you select the site number in A1, you must have
the rest of general info for this site displayed

Copy header row from other worksheet (without SiteNumber) below general
info. Into column A of second next row (I assume the header on second sheet
is in first row, and site number is in column A) enter the formula:
=IF(Sheet2!$A1=$A$1,Sheet2!B2,"")
and copy it over 3 columns to right and 1599 rows down. You will see the
detailed info for selected site, all other rows will be blank.

Select the range including all formulas and the empty row below detailed
info header, and set autofilter on. Set autofilter to 'Not empty' for some
column where always are some data on sheet2. Select leftmost cell with
autofilter arrows in it, and freeze panes.

It's all


Arvi Laanemets
 
If the functions in the file at http://home.pacbell.net/beban are
available to your workbook, you might want to consider something like
the following, which assumes your unique site numbers are in Column A of
Sheet1, and the data set is in A1:D1599 of Sheet2. Highlight, for
example, F1:I1599 and array enter

=Vlookups(K1,Sheet2!A1:D1599,{1,2,3,4})

Then if you enter into K1 of Sheet1 the cell address of the site number
(e.g., =A14), then the relevant data will appear starting at cell F1 of
Sheet 1.

Alan Beban
 
POSTED IN MISC.

try this: Adjust sheets & ranges to suit.

Sub listem2()
For Each cel In [sheet17!a21:a25] '1st list to add info to
mystr = ""
With Worksheets("sheet16").Range("b16:b22")'2nd sheet source
Set c = .Find(cel, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
mystr = mystr & c.Offset(, 1) & " "
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
'MsgBox mystr
cel.Offset(, 2) = mystr
End With
Next cel
End Sub
 
Back
Top