Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup and an External Reference (Excel 2003)

    I have a very large workbook I have been asked to review to insure the formulas are correct. The workbook has over 400 worksheets. One of the things I did was to insert some formulas to add up cells and cross foot across all the worksheets. To accomplish this I added a new worksheet on which I inserted a table I wanted to use for a vlookup. I named the range with the table qtr. I then selected all of the worksheets except the new one, inserted a column and entered the following formula:
    =VLOOKUP(B8,qtr,2,1)

    The formula was then copied down 150 rows. Everything worked correctly and I was able to sum up all the worksheets and find some errors. I returned the workbook to the owner who changed some values on various sheets. The owner tells me they did not change any formulas or add any worksheets. When I got the workbook back and opened it I received a message that there were links to other workbooks that could not be updated. After deleting sheets and columns I tracked the problem back to one worksheet and the formula included above. (If I erase the column in the one worksheet the problem goes away) I have looked at the formula and can find no difference in the formula on this sheet and the other 400 sheets. I then though I would get smart and copy the formula from another sheet that was not having a problem to the sheet with the problem. When I do the copy I get the following message:

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Vlookup and an External Reference (Excel 2003)

    Can't you just use the cell reference instead of the name qtr?

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup and an External Reference (Excel 2003)

    Hans,

    Thanks for the reply. Yes if I type in the absolute address for the range the error goes away. Of all the things I tried I never thought of the most basic.

    By the way if I change the formulas and save the sheet and then change the formula to include the name qtr the problem returns. I sure wish I could figure out what they had done.

    Thanks again for the help.

    M

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup and an External Reference (Excel 2003)

    Based on the message, I would speculate that you have more than copy of the name 'qtr' in the workbook. You can have as many as there worksheets. The names can be local (on a sheet) or global (available to all sheets). Check out Jan Karel <!profile=Pieterse>Pieterse<!/profile>'s " <img src=/S/free.gif border=0 alt=free width=30 height=15> Utilities" at JKP Application Development Services. and download the "Name Manager".

    This will find local and global names and allow you delete unwanted ones..

    Steve

  5. #5
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup and an External Reference (Excel 2003)

    Steve

    Thanks for the answer. The name was only on one sheet. However the utility did find an external reference to the name qtr in a worksheet of the same name on a different drive. That worksheet had been deleted. The name utility let me erase the external name and then everything worked correctly.

    I am still not sure what caused the problem but the utility corrected it.

    Thanks again

    M

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup and an External Reference (Excel 2003)

    Moving sheets/formulas from one book to another can create external references and external names.

    One place to spot them is if Edit - links is visible (not greyed out) indicating you have an external link. Many times you can "fix" this by selecting edit-links and changing the source to the current file...

    Steve

  7. #7
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup and an External Reference (Excel 2003)

    Steve,

    That is exactly what I needed. While the user tells me they did not move anything from one book to another I suspect that is what happened. I really appreciate the help.

    M

Posting Permissions

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