Return Values Single Criteria

[Rage]

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

[NBVC]

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))

[Rage]

Dear NBVC, i just cant thank you enough, it's not the first time you saved my day.

It works like a charm.

YOU RULE :P


Thanks

[Rage]

PS: NBVC is there any way of making it Auto-update the dropdown list ?

Cheers

[NBVC]

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

[Rage]

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

What i mean is that if i change the I3, i need to change the dropdownbox manualy in order to obtain the values from I3.
I wanted to update the first entry value of the dropdown box by itself. Without the need to press the box.

Did i make any sense ... lol ??

[NBVC]

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

Please Login or Register  to view this content.

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

[Rage]

Woot thanks... again.

What would i do without you