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.
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.
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By clogistics in forum Excel Formulas & Functions
Last Post: 12-21-2015, 08:39 AM
By ECYOJ in forum Excel General
Last Post: 11-25-2014, 11:02 PM
By gnikoli in forum Excel Formulas & Functions
Last Post: 05-31-2014, 11:53 PM
By KK1234 in forum Excel Programming / VBA / Macros
Last Post: 01-17-2014, 06:46 AM
By Berdman in forum Excel Programming / VBA / Macros
Last Post: 09-24-2012, 09:41 PM
By Alanwpf in forum Excel General
Last Post: 04-25-2012, 02:45 AM
By Justafriend in forum Excel General
Last Post: 07-20-2009, 12:07 AM
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1