Results 1 to 13 of 13

Thread: Excel V Lookups with 2 fresh files

  1. #1
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Excel V Lookups with 2 fresh files

    Lo people

    Why is it that, with 2 freshly made spreadsheets, even if they're saved, I cant use a V Lookup, unless I close them both and reopen them?

    I'm creating 15 or more per day, and in ever case, to use a VLOOKUP, I have to save, close, and reopen both files if they're new.

    Driving me mad. Any settings to prevent this?

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  2. #2
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel V Lookups with 2 fresh files

    have you looked at the linked sheets window thingie, and done the edit links?

    I don't have excel on this machine, and i'm jsut killing 5 mins before I dash off, but hopefully that vauge hint well get you off in the right direction!

    (also don't use multiple sheets like this, use the ODBC excel driver, and query them as if they where a database, that way you also have a migration path if the sheets become to big/slow)
    throw new ArgumentException (String, String, Exception)

  3. #3
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Re: Excel V Lookups with 2 fresh files

    Quote Originally Posted by TheAnimus View Post
    have you looked at the linked sheets window thingie, and done the edit links?

    I don't have excel on this machine, and i'm jsut killing 5 mins before I dash off, but hopefully that vauge hint well get you off in the right direction!

    (also don't use multiple sheets like this, use the ODBC excel driver, and query them as if they where a database, that way you also have a migration path if the sheets become to big/slow)
    edit links: not sure that's the issue. They're not linked yet.

    ( Ifeed a large volume of chassis numbers into a propriatory software package we use at work, take the returned list of data out as an excel doc, but then, to use it for a VLOOKUP, have to save it close it and reopen it)

    What's an ODBC excel driver? I'm using VLOOKUP, sometimes on 2500 rows, and grabbing what I need, then, using a quick COPY, and then PASTE as SPECIAL, Values Only, into the same column, to kil any links. It works a treat BUT this Save, Close, Reopen is doing my napper in.

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  4. #4
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel V Lookups with 2 fresh files

    http://support.microsoft.com/kb/257819

    its in VBA, might be overkill for you then, but thought i'd mention it.

    when you use another worksheet range in an excel formula, excel treats it as a linked sheet, this has a vast number of side effects, i'd hazard a guess thats what your up against.

    if its just getting rows from a series of sheets, then the ODBC driver is quite easy if you know a little VBA (don't worry, only a very little).

    The advantage then is you have abstraction, what is behind the ODBC driver could be anything, excel, access, sql server...........

    but I must be off again, as someone forgot to buy any milk, because he's a 'tard.
    throw new ArgumentException (String, String, Exception)

  5. Received thanks from:

    Zak33 (30-07-2010)

  6. #5
    Senior Member mikemikemi's Avatar
    Join Date
    Jan 2010
    Posts
    628
    Thanks
    20
    Thanked
    48 times in 48 posts

    Re: Excel V Lookups with 2 fresh files

    When you say it doesn't work what how does it not actually work?

    I'm no expert but do use excel on a daily basis.

    This might be stating the obvious, but if you're using vlookup from one book to another you can't reference across books unless both are open in the same instance of Excel (Unless you know the full file path of the other book).

  7. #6
    Now with added Ruffus Dog Tattysnuc's Avatar
    Join Date
    Feb 2006
    Location
    Liverpool
    Posts
    1,373
    Thanks
    283
    Thanked
    207 times in 133 posts
    • Tattysnuc's system
      • Motherboard:
      • Asus 570X Strix F
      • CPU:
      • 3900X @ Stock
      • Memory:
      • 32Gb Rysen C18 Corsair
      • Storage:
      • 2x 1TB nvme
      • Graphics card(s):
      • 2080 ti under Bykski water
      • PSU:
      • eVGA 850W
      • Case:
      • TT Core 5
      • Operating System:
      • Win 10 Pro
      • Monitor(s):
      • Lg 43"
      • Internet:
      • Utility Warehouse

    Re: Excel V Lookups with 2 fresh files

    Re: Excel V Lookups with 2 fresh files
    When you say it doesn't work what how does it not actually work?

    I'm no expert but do use excel on a daily basis.

    This might be stating the obvious, but if you're using vlookup from one book to another you can't reference across books unless both are open in the same instance of Excel (Unless you know the full file path of the other book).
    I'm still more familiar with Excel 2003, so maybe the version is important. When you create a spreadsheet with a "database" in it to be referenced by another sheet, the sheet with the vlookup will address it as [Filename.XLS]Sheetn!Range.

    For examples sake. 3 spreadsheets exist:
    Master - a table of names and addresses keyed off a code
    Name - a table of names which contain in the left most column, the code
    Address - Same as name, but with the address instead of a name

    On creating the link (i use ctrl+tab to switch between open sheets within Excel) the formula simply refers to the spreadsheet as the name shown in the title bar until it is save. On saving the spreadsheet this populates the name of the spreadsheet with the full path, so as long as you had the spreadsheets open on forming the link (in 2003) then when the master spreadsheet is save it will contain a link to the file by filename.

    If you are on 2007, and the file that is external has not been saved at any point, it will ask you if you wish to save "with links to unsaved external references". You have to click Yes to save.

    If you create the same 3 spreadsheets again
    Master
    Name
    Address

    this time when you open the MAster Spreadsheet there is a security warning just under the ribbon bar warnign that th efile contains links to external files. If you click the setting to "Enable this content" then the updated data flows through no probs.

    I've tested this on XP with office 2003 professional installed and office 2007 installed on a Windows 7 machine.
    Join the HEXUS Folding @ home team

  8. #7
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Re: Excel V Lookups with 2 fresh files

    OK, this is how it "fails"

    Create a spread sheet, with a load of rows and columns. Save it but DONT close it. Just save it.

    Create another spread sheet with another loads of rows and columns, and Save it too. but don't close it.

    Now try doing a VLOOKUP from one to the other, placing any info from one to another.

    You can't even show the VLOOKUP where to look... the window won't stay on top of the spread sheet to complete the table array section

    But close them both down... repopen.. and bingo, the VLOOKUP window does what it's supposed to and stays to the fore, allowing me to choose the table array.

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  9. #8
    Now with added Ruffus Dog Tattysnuc's Avatar
    Join Date
    Feb 2006
    Location
    Liverpool
    Posts
    1,373
    Thanks
    283
    Thanked
    207 times in 133 posts
    • Tattysnuc's system
      • Motherboard:
      • Asus 570X Strix F
      • CPU:
      • 3900X @ Stock
      • Memory:
      • 32Gb Rysen C18 Corsair
      • Storage:
      • 2x 1TB nvme
      • Graphics card(s):
      • 2080 ti under Bykski water
      • PSU:
      • eVGA 850W
      • Case:
      • TT Core 5
      • Operating System:
      • Win 10 Pro
      • Monitor(s):
      • Lg 43"
      • Internet:
      • Utility Warehouse

    Re: Excel V Lookups with 2 fresh files

    How are you entering the vlookup statement?

    If I go into the cell and enter:

    Vlookup(A1,Range,Column count, false)

    After entering the value A1, I then press Ctrl+Tab to switch to the workbook that contains the data, then select the range, type the comma, then switch back using the same method (ctrl+tab), then it works absolutely fine for me. I'm only workign with a small dataset, but the principle should be right, yes?

    Done on Office Ulimate 2007. Which version are you using?
    Join the HEXUS Folding @ home team

  10. #9
    Now with added Ruffus Dog Tattysnuc's Avatar
    Join Date
    Feb 2006
    Location
    Liverpool
    Posts
    1,373
    Thanks
    283
    Thanked
    207 times in 133 posts
    • Tattysnuc's system
      • Motherboard:
      • Asus 570X Strix F
      • CPU:
      • 3900X @ Stock
      • Memory:
      • 32Gb Rysen C18 Corsair
      • Storage:
      • 2x 1TB nvme
      • Graphics card(s):
      • 2080 ti under Bykski water
      • PSU:
      • eVGA 850W
      • Case:
      • TT Core 5
      • Operating System:
      • Win 10 Pro
      • Monitor(s):
      • Lg 43"
      • Internet:
      • Utility Warehouse

    Re: Excel V Lookups with 2 fresh files

    I think I've just replicated your problem inadvertently here at work on Office 2007, where I can't get the parent spreadsheet to update. How very odd!
    Join the HEXUS Folding @ home team

  11. #10
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel V Lookups with 2 fresh files

    you have tried the ctrl + alt + shift + f9 twice right?
    http://www.decisionmodels.com/calcsecretsg.htm

    not the best thou.
    throw new ArgumentException (String, String, Exception)

  12. #11
    Senior Member mikemikemi's Avatar
    Join Date
    Jan 2010
    Posts
    628
    Thanks
    20
    Thanked
    48 times in 48 posts

    Re: Excel V Lookups with 2 fresh files

    Kind of sounds like the issue I was eluding to.

    When you create the second sheet do you open new within the first sheet or click on an excel shortcut?

    I can replicate the problem (if it's the same), as it sounds like the two spreadsheets aren't open in the same instance of Excel therefore you can't Ctrl + Tab between them to set links.

    Try creating the second spreadsheet via Ctrl + N and seeing if that helps.

  13. #12
    Member
    Join Date
    Jul 2009
    Posts
    102
    Thanks
    24
    Thanked
    14 times in 11 posts
    • apmallett's system
      • Motherboard:
      • Asus P5K-E WIFI
      • CPU:
      • E8400
      • Memory:
      • 4GB DDR2-800 OCZ XTC Platinum
      • Storage:
      • 2 X 500GB Samsung Spinpoint Raid 0
      • Graphics card(s):
      • Asus 5770
      • PSU:
      • Corsair HX520
      • Case:
      • Antec P182
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • Samsung T220
      • Internet:
      • O2 - All I know!

    Re: Excel V Lookups with 2 fresh files

    Is one of the freshly made spreadsheets the exported file? I don't know a way round it apart from the save close open but I have had this problem when exporting from certain software packages.

    I know it doesn't solve the problem but might point you in the right direction?

  14. #13
    Now with added Ruffus Dog Tattysnuc's Avatar
    Join Date
    Feb 2006
    Location
    Liverpool
    Posts
    1,373
    Thanks
    283
    Thanked
    207 times in 133 posts
    • Tattysnuc's system
      • Motherboard:
      • Asus 570X Strix F
      • CPU:
      • 3900X @ Stock
      • Memory:
      • 32Gb Rysen C18 Corsair
      • Storage:
      • 2x 1TB nvme
      • Graphics card(s):
      • 2080 ti under Bykski water
      • PSU:
      • eVGA 850W
      • Case:
      • TT Core 5
      • Operating System:
      • Win 10 Pro
      • Monitor(s):
      • Lg 43"
      • Internet:
      • Utility Warehouse

    Re: Excel V Lookups with 2 fresh files

    Sussed it on mine on 2007.

    I've had to go into Excel options\Trust Centre then add the location where the file being referenced is located and add that to my trusted locations. Then when I update the sourced file, close it, then open the file witht he vlookups in, that now updates.

    Finally!
    Join the HEXUS Folding @ home team

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Shrinking Excel Files
    By [GSV]Trig in forum Software
    Replies: 11
    Last Post: 01-04-2010, 09:24 AM
  2. Deleting Vista System Files
    By wilksj01 in forum Software
    Replies: 4
    Last Post: 03-11-2008, 01:03 AM
  3. Adobe Premiere CS3 not accepting video files and out of synch
    By jezza7777777 in forum Help! Quick Relief From Tech Headaches
    Replies: 2
    Last Post: 08-06-2008, 11:11 AM

Posting Permissions

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