Displaying a list based on a Vlookup

[Gareth - Network analyst.]

Hi Guys/Girls

I have once again a problem that i need some assist on.

I have a sheet with multiple sites with Department codes and devisions phone
numbers etc. which i use for a offline referance of data.

where i have a problem is here, There are several sites with the same Site
ID, so what i would like to do idealy is this, on my controlling sheet i have
a cell that you enter your site code on(C3) now in the next cell (C4) i would
like it to have a listing that is based on all the values that are linked to
that site code the division for that site, and from there the cells below
telephone number etc can pull the data based on the list option.

I have a data workshhet with all the sites and all their information listed
with the site code being in column A and the department in column B.

 

[Gary''s Student]

see:

http://www.contextures.com/xlDataVal13.html

 

[Gareth - Network analyst.]

WOW my head wants to explode here.

OK cell C5 is validating based on sheet 1 data for site_code and i created
the name group for all of them as that Site_Code
So now i want (C6)

So I have this in data Validation
=IF(C5="",Site,INDEX(Site,MATCH(C5,CC_Code,0)))

So basically, If C5=[BLANK] it will show the entire list of name SITE.
otherwise. i want it to Index Site where there is a match from C% in the
Name CC_CODE field.

that much i get BUT for some reason i am only getting one value back. from
19 possible values.

my columns are as follows.
Column A = Sheet=DATA
CC_CODE [Name=CC_Code]
2000
2000
2000
1987
1687
etc
Column B = Sheet=Data
Site [Name=Site]
Bob
Charles
Richard
Sheriese
Chantel
etc

I want it when 2000 is entered on sheet to in cell C5 then cell C6 should
display the entire list (Bob - Richard) the formula im using now only seems
to bring the first value through.

Please help, my head hurts from all the thinking now.

 

[Gareth - Network analyst.]

OK never mind i figured that part out.
the validation source was:

=OFFSET(CC_Code_Start,MATCH(C5,CC_Code,0)-1,2,COUNTIF(CC_Code,C5),1)

ok but now problem 2, its prob around the same lines just setting the offset
by another value or 2 BUT can i get a lookup based on the C5 and C6 values to
return in column C7 without choosing data.

In other words, can i get C7 to do a lookup on C5 and C6 then return the
Value in that cell automatically. A dependency basically.

 

[Gareth - Network analyst.]

Ok figured that one out now to.

To do a lookup on Both Cells i used the following Formula

=INDEX(Division,MATCH(1,(C5=CC_Code)*(C6=Site),0))
and used the CTRL+SHIFT+Enter to close

For anyone thats looking at this here is whats happening.

=INDEX([NAME OF TABLE TO RETURN VALUE FROM],Match(1,([First lookup
Value]=[Name of table that this lookup is needed from])*([Second lookup
Value]=[Name of table that this lookup is needed from]),0))

took me long enought to figure it out properly, Just remember
CRTL+SHIFT+ENTER to enter the Formula or it wont take properly.