Linked sheet design theory

[Steve]

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

[Vacation's Over]

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

[Steve]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>