# 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

• Re: Linked Workbooks, Extreme Lag/Processing Time

I'm curious if anyone has any input on simplfiying this formula to reduce lag. Thank you.

• Re: Linked Workbooks, Extreme Lag/Processing Time

How is does your formula work? Because if you match returns a 17 or higher the index fails and there is no "2" option for the third option of match.

• 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

Can you post an example workbook?

• Re: Linked Workbooks, Extreme Lag/Processing Time

Here is an attached example.

## Files

• Re: Linked Workbooks, Extreme Lag/Processing Time

So this workbook is trying to access a closed workbook? Can you use macros?

• 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

Do you have a copy of "Example.xlsm" that I could have? (Example.xlsm referring to the document you mentioned in your formula)

• Re: Linked Workbooks, Extreme Lag/Processing Time

A few posts up, I did include a copy of my example. Did you need another document as well?

• Re: Linked Workbooks, Extreme Lag/Processing Time

Well I was just wondering what you formulas were linking to because as far as I can see every row of your formula will have the exact same data

• Re: Linked Workbooks, Extreme Lag/Processing Time

Ahhh, well, here is 'Test', they should link together to show you what is happening.

## Files

• Re: Linked Workbooks, Extreme Lag/Processing Time

So exactly what data are we looking for then extracting from Text.xlsx?

• 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

That would make more sense, and definately save on processing time. With security problems, it would have to be assigned to a key, not an automatic macro.

• 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

That works perfectly! Thank you.

• 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.