Linked Workbooks, Extreme Lag/Processing Time

  • Hello all,


    I have been working on this linked project database through Excel and was curious if a more efficient formula could be created. My current formlua is below...


    =IF($A5>0,INDEX('C:\[Example.xlsm]Sheet2'!$A5:$P5,
    MATCH($A5, 'C:\[Example.xlsm]Sheet2'!$A5:$A$200000, 2),0), "")



    It essentially takes the data, cell by cell, from one workbook to another. However, having thousands of rows, with range A-P having this formula in each cell, it takes over 4 minutes to process.


    Any help would be appreciated! Thank you.

  • Re: Linked Workbooks, Extreme Lag/Processing Time



    Bump. ADDED BY ADMIN;


    See Excel best practices

  • Re: Linked Workbooks, Extreme Lag/Processing Time


    Ranges A:P1 to A:P2000 includes numbers from 1 to 2000 in both documents. This formula pulls whatever matches for each number and then I can sort through what is needed. However, since every cell has a formula applied, it gets rather heavy on file size and processing time.

  • Re: Linked Workbooks, Extreme Lag/Processing Time


    It's linked to a document that updates every day. It would pull the data from the workbook, serving as a 'middle' document. It's sorted based on what we need, but there is a third document that keeps the data as a form of 'master list' utilizing the same fomula.


    I am allowed to use macros on this one.

  • Re: Linked Workbooks, Extreme Lag/Processing Time


    'Example' would extract everything from 'Test' and then we would sort the data that is needed. I forgot to mention that each row is a line of data that corresponds. The entire row 1 is relevant, row 2, and so on.

  • Re: Linked Workbooks, Extreme Lag/Processing Time


    Ok. So since Test is already in order 1-Number and Example is in order 1-Number, would a macro that just copied the whole sheet to the open workbook should work, right?

  • Re: Linked Workbooks, Extreme Lag/Processing Time


    Here is some VBA code. Hopefully it's pretty straight forward. As far as the security goes there is a password to activate the macro. You also need to put a password on the macro itself so people can't look at the password.


    If a person really wanted to they could use a hex editor and decipher the code, but the way your code looks this way is more secure.


  • Re: Linked Workbooks, Extreme Lag/Processing Time


    Ok, a new question for you, rather, a bit of evolution from the current macro code.


    Workbook1 has all the data and it needs to be copied/paste to Sheet2 of Workbook1, but also Copy/paste to Workbook2 Sheet1 as well. Is this an easy modification? The security password could also be removed since the documents are placed in a hidden drive.


    Thank you for your help!