Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying a list based on a Vlookup
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying a list based on a Vlookup
see:
http://www.contextures.com/xlDataVal13.html -- Gary''s Student - gsnu200752 "Gareth - Network analyst." wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying a list based on a Vlookup
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. "Gary''s Student" wrote: see: http://www.contextures.com/xlDataVal13.html -- Gary''s Student - gsnu200752 "Gareth - Network analyst." wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying a list based on a Vlookup
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. "Gary''s Student" wrote: see: http://www.contextures.com/xlDataVal13.html -- Gary''s Student - gsnu200752 "Gareth - Network analyst." wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying a list based on a Vlookup
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)) :P took me long enought to figure it out properly, Just remember CRTL+SHIFT+ENTER to enter the Formula or it wont take properly. "Gareth - Network analyst." wrote: 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. "Gary''s Student" wrote: see: http://www.contextures.com/xlDataVal13.html -- Gary''s Student - gsnu200752 "Gareth - Network analyst." wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying how many results in a vlookup | Excel Discussion (Misc queries) | |||
Displaying cell range with Vlookup | Excel Discussion (Misc queries) | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
Looking up and displaying a icon based on a cell value | Excel Discussion (Misc queries) | |||
Displaying data based on a TRUE/FALSE value in a cell | Excel Discussion (Misc queries) |