+ Reply to Thread
Results 1 to 2 of 2

Vlookup Across Worksheets copied out of a workbook eventually returns #NA

  1. #1
    Registered User
    Join Date
    Perth, Australia
    MS-Off Ver

    Vlookup Across Worksheets copied out of a workbook eventually returns #NA


    I have a costing workbook whereby different costing worksheets vlookup steel prices that are tabled in their own worksheet.
    To estimate a project, we copy some of the costing worksheets (as many as we need for that particular project) out into their own workbook.
    The steel costs still refer back to the original workbook, so it works great.
    However after enough time has passed (usually months) the steel cells return the #NA error.
    I suspect this could potentially be to do with during this time, we've changed something on the steel cost table, whether it be adding a column, row, steel size etc. but not really sure.

    If this is the case, is there any way we can prevent this from happening (i.e. locking once copied or a better link)?
    it is quite painful to have to manually re-enter steel costs once you have won a project or it comes back out for re-pricing.
    Ideally when this happens we would open the original project workbook to automatically updated steel costs and be able to see current margin for that job.


  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2312 (Windows 11 22H2 64-bit)

    Re: Vlookup Across Worksheets copied out of a workbook eventually returns #NA

    Welcome to the forum.

    My instinct would be to suggest that you use INDEX MATCH instead of VLOOKUP so that you can specify column headers. However, given your workflow, you may still end up running into issues with the integrity of your data. Maybe using PowerQuery to extract the data you need would be a better approach, but really there's not enough information here to make that call.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT release number like 2202) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can a VLOOKUP formula be copied to another sheet within the workbook?
    By clogistics in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-21-2015, 08:39 AM
  2. [SOLVED] Copied formula returns 0 value
    By ECYOJ in forum Excel General
    Replies: 1
    Last Post: 11-25-2014, 11:02 PM
  3. Copied data and array formula returns #DIV/0!
    By gnikoli in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-31-2014, 11:53 PM
  4. [SOLVED] When data is copied to another workbook, hyperlinks as not copied or they don't work
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-17-2014, 06:46 AM
  5. Applying Copied Image to All Worksheets in Workbook.
    By Berdman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2012, 09:41 PM
  6. Replies: 2
    Last Post: 04-25-2012, 02:45 AM
  7. Replies: 1
    Last Post: 07-20-2009, 12:07 AM

Tags for this Thread


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