Linked sheet design theory
 

Hi

I'm creating a project to parse a .txt file of attendance information into a
workbook. Details include EmplID. There is a need to link this to related
data.

First test to link to one workbook using code to generate Index & Match
formulae produced relatively slow results (about a minute or so to link
around 4000 lines. Is there a better way to solve this?

Steve

Linked sheet design theory
 

need lots more information on how and what you did. post code

otherwise generic:
application.screenupdating = false 'then true
application.calculation = xlmanual ' then xlautomatic
write to an array then write all to sheet

search this group for "efficient"

"Steve" wrote:

Hi

I'm creating a project to parse a .txt file of attendance information into a
workbook. Details include EmplID. There is a need to link this to related
data.

First test to link to one workbook using code to generate Index & Match
formulae produced relatively slow results (about a minute or so to link
around 4000 lines. Is there a better way to solve this?

Steve

Linked sheet design theory
 

I always stop screenupdating and calculations.

Didn't bring the sheet home from work. It's more complex than the following
but this should give the general idea:

For i = 2 to RowCount
Range ("B" & i).formula = ("=INDEX(Filepath!Database,MATCH(B" & i &
",Database),1)"
Next i

Will running to array be quicker? Might it be quicker to copy across the
external reference data to a spare worksheet and reference it there before
turning formulae to value and deleting imported data?

At most, I envisage 10,000 lines or data and 500 lines of lookup.

Steve

"Vacation's Over" wrote in message
...
need lots more information on how and what you did. post code

otherwise generic:
application.screenupdating = false 'then true
application.calculation = xlmanual ' then xlautomatic
write to an array then write all to sheet

search this group for "efficient"

"Steve" wrote:

Hi

I'm creating a project to parse a .txt file of attendance information
into a
workbook. Details include EmplID. There is a need to link this to related
data.

First test to link to one workbook using code to generate Index & Match
formulae produced relatively slow results (about a minute or so to link
around 4000 lines. Is there a better way to solve this?

Steve