+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

trying to create a cross reference tool with Vlookup formulas

  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    trying to create a cross reference tool with Vlookup formulas

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: trying to create a cross reference tool with Vlookup formulas

    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!!
    Glenn



  3. #3
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

    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

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: trying to create a cross reference tool with Vlookup formulas

    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.

  6. #6
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

    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
    Attached Files Attached Files

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: trying to create a cross reference tool with Vlookup formulas

    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.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

    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!

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: trying to create a cross reference tool with Vlookup formulas

    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.

  10. #10
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

    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
    Attached Files Attached Files

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: trying to create a cross reference tool with Vlookup formulas

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

  12. #12
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: trying to create a cross reference tool with Vlookup formulas

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

  13. #13
    Registered User
    Join Date
    12-17-2019
    Location
    Seattle
    MS-Off Ver
    Office360
    Posts
    2

    Re: trying to create a cross reference tool with Vlookup formulas

    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?
    Last edited by AliGW; 12-18-2019 at 01:08 PM. Reason: Please don't quote unnecessarily!

  14. #14
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

    sorry, try again
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: trying to create a cross reference tool with Vlookup formulas

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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1