1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: a new vlookup problem

Discussion in 'Business Applications' started by Lex.Luthor, Jan 21, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    I've searched most of the forum and Mr. excell's site and I think that I have missed the train somewhere. From the upload, this is a transfer log that I am working on. Based on the past vlookup formulas that I have used (Thank you Keebellah and others), this one uses the vllokup over many spread sheets. I have entered a few pages that I have started for this form and will be adding more names or sheets as time pases. The end result that I am looking for is on the main page, being able to enter a transfer number and the information pops up. The key is the transfer number, no two tranfer numbers are ever going to be alike. I thifnk that I can grasp the concept for just a single page, but I think that I am missing something for several; pages.

    Thank you for your time and effort.

    Luther Haynes
     

    Attached Files:

  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    you can add more as your tab grows.
    =IFERROR(VLOOKUP($A$4,arnold!B:B,1,0),"")&IFERROR(VLOOKUP($A$4,beck!B:B,1,0),"")&IFERROR(VLOOKUP($A$4,FR!B:B,1,0),"")

    Or create an additional column called "Ship From" and merge all the tabs into one.
     
  3. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    Garf13LD,

    Thank you for the reply. I am a little unclear about merging the cells. If I did that, would the data within the cells be changed as well.

    I am going to play around with the formula to see if I can also be able to list the ship from, dest, date, verified by and the manifest number to do the same when I enter the transfer number in.

    Thank you,

    Luther Haynes
     
  4. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    This is how I suggest merging if keeping them separate is not required.
    Also keep your lookup column to be the first column where possible.
    In your previous example, the date cannot be lookup becos it is before the ID.
    Another thing is if you change the columns of the list, do change your vlookup col number accordingly.
    eg. =vlookup(val, list, col number, 0)
     

    Attached Files:

  5. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    I tried that once before by placing all of the data on one sheet to simplify the formula. That was the easy part. What I am after is to see if the vlookup formula has the capability of looking at other sheets that are within the same filename. The data that I am using will also be looked at on an individual basis at to where all the transfers are going to, this is why that there are several sheets. I keep the locations seperate.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    You'll have to add the sheet reference for every separate VLOOKUP
     
  7. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    If I understand correctly:

    For each sheet, I would use the formula of :

    =IFERROR(VLOOKUP($A$4,arnold!B:B,1,0),"")

    use the "&" between each formula and change the arnold! B:B reference for each added.

    this would give me the transfer number

    now, for the tricky part, when I use this formula and change the arnold reference to show the other information, i get nothing.

    I did remove the ship date because I only need the other information to be shown

    I must be missing something.
     
  8. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Like I said, it will be messy.
     

    Attached Files:

  9. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    I'll get my large bottle of aspirins. :eek:
     
  10. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    Garf13LD,

    I've tried and altered it to include the extra few pages that I had and field tested it through and through.

    Thank you.
    Thank you
    Thank you.

    I did not see using the function indirect would be used here. I still have much to learn. I did add an extra column in the fields for notes, added and modded the formula to find the notes and it works very well.

    I hope that this message makes your day!

    (y)

    Have a Great day!

    Luther Haynes
     
  11. Lex.Luthor

    Lex.Luthor Thread Starter

    Joined:
    Jun 28, 2008
    Messages:
    263
    On a side note, I hope that anyone that searches for this answer to the problem that I had, if you're reading this, these people on this forum are talented in their own special way of helping other people.

    Please join us!
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086325

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice