+ Reply to Thread
Results 1 to 16 of 16

Making vlookup persistent

  1. #1
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Making vlookup persistent

    Hello,

    I've been searching for a way to make the vlookup function persistent in a cell, or at least find an alternative!

    What I am trying to do is make it so that when I enter a value in A1, then B1 automatically references a table and enters a corresponding value. I can do this using vlookup, however once it has found the value, my formula disappears.

    I am thinking that a macro could be used to make the cell update the information as needed, but I am not sure how I would program it. Any help would be appreciated!
    Last edited by NBVC; 11-07-2011 at 05:06 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Making vlookup persistent

    I am not sure I can help you to much with the VBA part, if required, but I don't understand this part:

    I can do this using vlookup, however once it has found the value, my formula disappears.
    How is the formula disappearing? Is there already a macro that replaces the cell content? A formula can't disappear after it retrieves a value, unless forced by deletion or via macro.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Quote Originally Posted by NBVC View Post
    I am not sure I can help you to much with the VBA part, if required, but I don't understand this part:



    How is the formula disappearing? Is there already a macro that replaces the cell content? A formula can't disappear after it retrieves a value, unless forced by deletion or via macro.
    Sorry, I am being a little unclear. The formula isn't disappearing per se, but rather once it find the value it is looking for, the cell then becomes that value. I have not used any macro to this point, this is simply me typing the vlookup formula into a cell.

    For instance, if I write =vlookup(A1, B1:E10, 4) in cell Z1, then it goes and finds that value is "X". If I select the cell again, then the cell doesn't read the vlookup formula, it just says "X." If I change the value in A1, then I would have to renter the vlookup formula in Z1 to get it to find the new value I am looking for.

    It is kinda hard to explain for me I guess If this still isn't making sense, I will try and clarify it a little more.

  4. #4
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Making vlookup persistent

    Have you turned off auto calculations? when you enter or change to a new lookup value the result cell should change automatically

    Go into Options, and look for Workbook Calculations and make sure it's set to Automatic and not manual
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  5. #5
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Quote Originally Posted by scottylad2 View Post
    Have you turned off auto calculations? when you enter or change to a new lookup value the result cell should change automatically

    Go into Options, and look for Workbook Calculations and make sure it's set to Automatic and not manual
    Okay, I will check that out...

    This is excel 2002, incidentally, so it may not have the functionality I need I suppose...

    EDIT: okay, it is set to automatic...

    The thing is, if I enter a new vlookup into the cell, it works as intended.

    Example...

    Say I have a chart...
    Date Stock# Year Make Model Color
    10/24/11 J3491 2000 Chevrolet Blazer Red
    10/24/11 J3492 2001 Pontiac Grand Am Gold
    10/24/11 J3493 2001 Buick Century White
    10/24/11 J3494 1999 Pontiac Grand Prix Silver
    10/27/11 J3495 2003 Mercury Sable Wagon Red
    10/27/11 J3496 2003 Dodge Durango Grey
    10/27/11 J3497 2000 Dodge Grand Caravan Gold
    10/27/11 J3498 2000 Ford Explorer Green
    10/27/11 J3499 2002 Buick Regal Silver
    10/27/11 J3500 2002 Oldsmobil Alero Red
    11/03/11 J3501 1999 Ford Escort ZX2 Green
    11/03/11 J3502 1999 Plymouth Breeze Tan
    11/03/11 J3503 2002 Pontiac Grand Prix Blue
    11/03/11 J3504 1998 Saturn SL1 Green
    11/03/11 J3505 2000 Ford Taurus Green
    11/03/11 J3506 1999 Buick Park Avenue Beige

    (the spreadsheet doesn't copy great, but you get the idea...)

    On a separate worksheet, I want to be able to put in a stock number in A1, and have B1:E1 automatically spit out the year, make, model, and color.

    Currently, the only way it works is if I manually type in a vlookup in B1, C1, etc. to pull out the information that I want. It is slow, and I don't want to have to retype the formula each time. If I change the stock number from J3500 to J3501, then I have to redo them all over again.
    Last edited by 91TwighlightGT; 11-04-2011 at 02:19 PM.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Making vlookup persistent

    It's in the Tools menu... I am pretty sure it's in 2002 too.

  7. #7
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Quote Originally Posted by NBVC View Post
    It's in the Tools menu... I am pretty sure it's in 2002 too.
    Yes, I got it, thanks.

    I edited my post above.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Making vlookup persistent

    You should enter the Stock# in a cell and reference that cell in your VLOOKUPs

    e.g.

    =VLOOKUP($A1,'ChartSheet'!$B:$E,COLUMNS($A$1:B$1),FALSE)

    copied across and/or down if you got a list in column A.

    Where A1 contains the first Stock # to look up in a sheet called ChartSheet... adjust as necessary.

  9. #9
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Quote Originally Posted by NBVC View Post
    You should enter the Stock# in a cell and reference that cell in your VLOOKUPs

    e.g.

    =VLOOKUP($A1,'ChartSheet'!$B:$E,COLUMNS($A$1:B$1),FALSE)

    copied across and/or down if you got a list in column A.

    Where A1 contains the first Stock # to look up in a sheet called ChartSheet... adjust as necessary.
    Well, the issue is that I can't copy it down because I have to print out the page with the information from the stock number. Long and short, I put in the stock number and all the info, print that page, then put in another stock number and all the info, and print that page, etc.

    So, it really needs to work with me putting in the single stock number, and the other cells then having the correct information applied.

    Perhaps I am just going about it the wrong way with vlookup...

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Making vlookup persistent

    I said and/or copy down.. you don't have to copy down.

    The A1 doesn't have to be in the same row..

    Say you input the stock # in A1

    you can retrieve the info in say D2 with:

    =VLOOKUP($A$1,'ChartSheet'!$B:$E,COLUMNS($A$1:B$1),FALSE)

    copied across

    or just

    =VLOOKUP($A$1,'ChartSheet'!$B:$E,2,FALSE)

    and change the 2 to 3 to get next column, etc...

  11. #11
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Making vlookup persistent

    Can you upload a sample?

    say you put this formula in A2 and drag along to E2, whatever data you have stored in your table this should pull through the coresponding row of expected return based on the stock number you enter in A1

    =VLOOKUP($A1,'ChartSheet'!$B:$E,COLUMNS($A$1:A1),FALSE)

    Are you expecting a whole table full of returns or a single row?

    please attach a sample of what you have and what you want as an output

  12. #12
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Quote Originally Posted by scottylad2 View Post
    Can you upload a sample?

    say you put this formula in A2 and drag along to E2, whatever data you have stored in your table this should pull through the coresponding row of expected return based on the stock number you enter in A1

    =VLOOKUP($A1,'ChartSheet'!$B:$E,COLUMNS($A$1:A1),FALSE)

    Are you expecting a whole table full of returns or a single row?

    please attach a sample of what you have and what you want as an output
    Okay, when I get a minute I will provide a sample.

    To throw another wrench into things, the way that I need to do things will not allow me to drag as you guys are suggesting. Basically, I am using excel as both a spreadsheet (for inventory documentation) and a form writer. The nature of the form writing is that there are pieces of data in multiple different rows and cells, and when I print it out it matches up to whatever form I am printing. Totally confusing to write about, so I will post it up with the sample. The long and short is that I can't drag the things to make it easy.

    Sorry to be a PITA



    To be as clear as I can, I need to be able to input the stock number into a single cell, and the other cells to then pull data from the table in reference to that stock number. So, one cell will pull make, one will pull model, etc, but the only input that I make is the stock number. My hope is to make it automated so that I do not have to input every single piece of information each time that I select a new stock number.

    Thanks for the help so far guys!

  13. #13
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Sample.xls
    Okay, there is the sample.

    Focus on the first two worksheets, which is the inventory list and the "dealjacket."

    The inventory list includes the date, make, model, and color of the vehicles.

    On the dealjacket, there is a spot for two stock numbers. Ignore the left stock number as it is not useful in the sample.

    The right side stock number is the input that I would be making, right now set as stock number 3401. As you tab through, the make, model, etc. are all obvious. So, what I need to have is when I input the stock number, the make/model/etc. all pull up the corresponding information.

  14. #14
    Registered User
    Join Date
    10-07-2011
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Making vlookup persistent

    hello,

    i see two ways of doing this... one is to use vlookup function, and second using index, match functions.

    i've attached both ways in the spreadsheet. i suggest the second way, since it allows for more future options. with index, you may change the titles of each column, move them, etc, and still get the value, without regards to which actual column it's in.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    in this case, for ease, you may name the stock # prompt as Stock, or something similar, so that it's easier to click and drag (the ability maintains the variable, while moving only the associated other factors). for further ease, such that using the AutoFill -click and drag- is more compatible, you may have a total of 4 "Insert->Name->Define"s. 1)StockChart - selected as columns A through E. 2) Stock - selected as the cell in which you wish to enter the stock number. 3) Row - selected as A:A. & 4) Column - selected as 1:1.

    this will make it extremely easy for clicking and dragging, not to mention the code:

    Please Login or Register  to view this content.
    click and drag as needed... and a lot easier to read
    Attached Files Attached Files
    Last edited by Apelcius; 11-04-2011 at 06:01 PM.
    --Apelcius--
    Learn. Share. Rinse, Repeat.

  15. #15
    Registered User
    Join Date
    11-04-2011
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Making vlookup persistent

    Ahh, that looks like it will work perfectly!

    Thanks for all the help guys!

  16. #16
    Registered User
    Join Date
    10-07-2011
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Making vlookup persistent

    no problem please remember to mark forum as [solved]

+ Reply to Thread

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