# Office 365 >  > [SOLVED] trying to create a cross reference tool with Vlookup formulas

## yonsei

Hi , need help to create a cross reference tool,

In Col. A, I want a drop down menu so you can pick the obsolete part#, that will populate col. B (description), then in Col C, I want to display a range of parts associated with the part# in Col. A , but in a second drop down menu. Once you select the part# in the 2nd drop down menu, I want to display the cross referenced part# to show up in Yellow

hope that makes sense, see attached excel file

thanks

----------


## Glenn Kennedy

You have attached a file with links (e.g. A4) to files on your PC/Server.  Please amend your sheet to have all information needed IN the file.  Also, please indicate where we are supposed to FIND all these bits of information that you want returned!!

----------


## yonsei

Hi Glenn,
sorry, I named the data tab wrong , it should be "Data - Overloads"

Col. A of "cross reference" is a pull down list from Col A of "Data - Overloads"
Col. B of "cross reference" gets info from Col. B of "Data - Overloads"
Col. D of "cross reference" gets info from Col. D of "Data - Overloads"
Col. E of "cross reference" gets info from Col. E of "Data - Overloads"
Col. F of "cross reference" gets info from Col. E of "Data - Overloads"

see file Book4.xlsx

----------


## yonsei

Oh, and I forgot ,
Col. C of "cross reference" is a vlookup of Col. A "cross reference" pointing to
Col. C of "data - overloads"

but I want col. C of cross reference to be a drop down list

----------


## Glenn Kennedy

Your file is unchanged.  It contains formulae and references to an external sheet two which I have no access.  Please amend and attach, as requested.

----------


## yonsei

ok, sorry, i've fixed my file now so it has the correct formulas that point to this file only.

for the most part , it works how I want it , but just one thing doesn't work correctly.

If I select part# 48GC18AA4, in column C of the cross reference tab, in the drop down list, you should only be able to select part#s that begin with "K"
then if you select part# 48DA18A, in col C of the cross reference tab, in the drop down list, you should only be able to select part#s that begin with "E"

thanks

----------


## Glenn Kennedy

Feel free to change the sheet names back to what they were.  I had to change them to something short to facilitate checking!!  Now I can't remember what they were....
I created 3 Named Ranges (CTRL-F3 to view/edit) to automate things.

1.   A unique list of Bimetal Part numbers on sheet Help:
=IFERROR(INDEX(Bimetal,MATCH(0,INDEX(COUNTIF($A$1:$A1,Bimetal),0),0)),"")

where Bimetal automatically slects non-blank rows (no blank cells!!) in Data column A using this named range:
=Data!$A$2:INDEX(Data!$A:$A,MATCH("zzz",Data!$A:$A))

2.  A Named range (Unique) to select ONLY the unique values for the DV in Col A of XR:
=Help!$A$2:INDEX(Help!$A$2:$A$20,SUMPRODUCT(--(LEN(Help!$A$2:$A$20)>0)))

3.  A Named Range (ColC), to select only the relevant Column C values:
=INDEX(Data!C:C,AGGREGATE(14,6,ROW(Data!$C$2:$C$20)/(Data!$A$2:$A$20=XR!A4),1)):INDEX(Data!C:C,AGGREGATE(15,6,ROW(Data!$C$2:$C$20)/(Data!$A$2:$A$20=XR!A4),1))

4. IFERROR traps to return blanks when A and/or C are blank.

----------


## yonsei

that is amazing, thank you very much!
I not sure if I understand what you did, I will have to study it closer.
thanks again for the help!

----------


## Glenn Kennedy

Feel free to ask Qs later.  For now...

You're welcome.


If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as *SOLVED*. 

It'd also be appreciated if you were to click the *Add Reputation* button at the foot of any of the posts of all members who helped you reach a solution.

----------


## yonsei

Hi Glenn,
I spoke too soon. I guess I didn't explain fully my functionality, my fault....

I have loaded my full database into the "Data" tab and I have put all my unique part#s into Col A of the XR tab.
What I didn't explain before is this:
* If I select a unique part# in Col A, the drop down list in column C should show the corresponding K and E parts for that unique part#.

this is what i am not sure how to do

----------


## Glenn Kennedy

You have clearly attached the wrong file.  There are two part numbers.  One has ONLY K parts and the other ONLY E parts.

----------


## Glenn Kennedy

Please amend the list in MY file.  I have no wish to set up all the formulae again, from scratch.

----------


## shade206

What if he has Multiple tabs, lets say "data" and "data 1", he wants that initial drop down menu (Bimetal Overload part#) to reference them all, and then based on the selection he makes it will pull from the correlating tab. For example say Part# 48DA18A has all of its information on a new tab labeled "Data 1" instead of it being on the same tab. What would the formula in B4 of the XR tab look like?

----------


## yonsei

sorry, try again

----------


## yonsei

here is the file you sent me with just my full database, all of your formulas are intact

----------


## yonsei

ok, looks like I was able to figure out my problem, I needed to extend my look up ranges and I also needed to add a key.
Thanks for pointing me in the right direction. I will mark this post as solved.

----------

