Results 1 to 6 of 6

Thread: Vlookup

  1. #1

    Cool Vlookup



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am using Vlookup in one of xls files that users have it on their desktop to search for variable data located on our server that I update it monthly. The user file is about 1MB while the server file is about 6MB. Because of Vlookup, the user file expanded almost to 6.5MB to contain the server file.

    According to our company internal policy, this size of the user file (6.5MB) is not recommended for different reasons. Is there any other formula or method that can provide the same function in different than Vlookup’s technique? I appreciate your support. Thank you. :)

  2. #2
    i think after you have already done the vlookup you copy paste same as values. or just keep the formula on only the first row and paste the others as values. sometimes i do it like that because me also we are not allowed to keep file with larger size.

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Unless you copied the VLOOKUP formula into thousands of cells, it should not have that effect. If you do have thousands of them, then the chances are that the file size is down to the number of formulas as much as what the formulas are (in terms of length).
    Circumference of a circle = 2πr²



    ²the circle's radius

  4. #4
    =IF($B6=7,VLOOKUP($F$5,'\\Server name & file bath\[File name.xlsx]July-Data'!$A$9:$R$7915,3,FALSE),

    IF($C6=8,VLOOKUP($F$5,'\\Server name & file bath\[File name.xlsx]Aug-Data '!$A$9:$R$7915,5,FALSE),

    IF($C6=9,VLOOKUP($F$5,'\\ Server name & file bath\[File name.xlsx]Sep-Data'!$A$9:$R$7915,7,FALSE), "Data is not Available yet")
    ) )

    The server file contains 8 tabs; from May until Dec where each month data is in a separate tab.
    So, if the user entered July in $B6 in the user file, the Vlookup will search in July tab of the server file.

    I do not have any formulas in the server file. Thank you.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    I'm not totally clear on this. Which file is 6.5MB?

    Honestly, from what I see here I'd probably be leaning to setting up a database and using a SQL query to pull out the info you're looking for.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Can you please help? I would appreciate your help. Thank you.

Posting Permissions

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