Thread: Return Values Single Criteria

Hi all,


I'm trying to develop a way of joining 2 diferent spread Sheets.


Here is what i need help with, I want that on Sheet2:

House Number = any number

Lookup ( House Number Value ) on the Sheet1! Table for rows B / C / D / E

Return multiple values and use those values on a dropdown box in B3
So that B4 / B5 / B6 change according to B3

I've attached an example

See attached for an option:

In Bookings!F10 added a helper column with formula:

=COUNTIF(A$10:A10,House!$I$3) copied down to count matches based on entry made in House!I3

In House!N1: =MAX(Bookings!F:F) to show max number of matches

In N2: =IF(ROWS($A$1:$A1)>$N$1,"",INDEX(Bookings!B:B,MATCH(ROWS($A$1:$A1),Bookings!F:F,0)))

copied down as far as you want to extract matches...

Then go to Formulas|Define Name and enter Name: Arrivals with formula:

=OFFSET(House!$N$1,1,0,COUNT(House!$N:$N)-1,1)

Then go to cell B3 and then Data|Validation, choose List and enter: =Arrivals

in B4: =IF($B$3="","",VLOOKUP($B$3,Bookings!$B$10:$E$20,2,0))

in B5: =IF($B$3="","",VLOOKUP($B$3,Bookings!$B$10:$E$20,3,0))

in B6: =IF($B$3="","",VLOOKUP($B$3,Bookings!$B$10:$E$20,4,0))

Not sure what you mean... the list updates based on the entry you make for House No. in I3 of House! Sheet.

Right click the tab name and select View code, past the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Range("b3").Value = Range("N2").Value
End Sub

Then close the Editor and test by changing I3...