importing data from closed Excel workbook

[SteveC]

In cell G2 I have the following formula:

=INDEX('[Database -- AR and EQ.xls]Stock
Lookup'!$G$2:$G$6000,MATCH($B2,'[Database -- AR and EQ.xls]Stock
Lookup'!$B$2:$B$6000,0),1)

I other columns (H through Q), I have similar formulas but linking to
different closed workbooks.

When 'Database -- AR and EQ.xls' is closed, the formula is much longer than
the one posted above because it references folders on the hard drive:

=INDEX('U:\SC\Research\Other\Hot List\Hot List Database\[Database -- AR and
EQ.xls]Stock Lookup'!$G$2:$G$6000,MATCH($B2,'U:\SC\Research\Capital IQ\Hot
List\Hot List Database\[Database -- AR and EQ.xls]Stock
Lookup'!$B$2:$B$6000,0),1)

I have a macro that will pull down this formula about about 6000 rows. I
have similar formulas in other columns. Then excel copies and pastes the
values.

As you can imagine, updating these links from the closed workbook takes a
very, very long time. Opening the "Database -- AR and EQ.xls" workbook
before refreshing the links probably won't work for me because a commercial
add-in I need to use interferes with the open process for some reason.

I'm wondering if there is a faster way to update the data than this? I
don't necessarily need to pull down and copy paste value the formulas; I just
need the data in the right place based on the cell references the formulas
refer to.

Thanks for thinking about this.

SteveC

 

[Martin Fishlock]

Hi Steve:

If you open the workbook before copying and pasting it should work fast.
Another way is to use a macro that get the result and then pastes a hard
value in. Although this does not dynamically update the next time.

As an alternative you could copy the sheets in to the working speaksheet and
link there.

Then everytime you want to update you just copy the data into the working
workbook.

This will improve the lookups.

 

[SteveC]

Martin, good suggestions, thanks. Will give them a shot.

Hi Steve:

If you open the workbook before copying and pasting it should work fast.
Another way is to use a macro that get the result and then pastes a hard
value in. Although this does not dynamically update the next time.

As an alternative you could copy the sheets in to the working speaksheet and
link there.

Then everytime you want to update you just copy the data into the working
workbook.

This will improve the lookups.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

In cell G2 I have the following formula:

=INDEX('[Database -- AR and EQ.xls]Stock
Lookup'!$G$2:$G$6000,MATCH($B2,'[Database -- AR and EQ.xls]Stock
Lookup'!$B$2:$B$6000,0),1)

I other columns (H through Q), I have similar formulas but linking to
different closed workbooks.

When 'Database -- AR and EQ.xls' is closed, the formula is much longer than
the one posted above because it references folders on the hard drive:

=INDEX('U:\SC\Research\Other\Hot List\Hot List Database\[Database -- AR and
EQ.xls]Stock Lookup'!$G$2:$G$6000,MATCH($B2,'U:\SC\Research\Capital IQ\Hot
List\Hot List Database\[Database -- AR and EQ.xls]Stock
Lookup'!$B$2:$B$6000,0),1)

I have a macro that will pull down this formula about about 6000 rows. I
have similar formulas in other columns. Then excel copies and pastes the
values.

As you can imagine, updating these links from the closed workbook takes a
very, very long time. Opening the "Database -- AR and EQ.xls" workbook
before refreshing the links probably won't work for me because a commercial
add-in I need to use interferes with the open process for some reason.

I'm wondering if there is a faster way to update the data than this? I
don't necessarily need to pull down and copy paste value the formulas; I just
need the data in the right place based on the cell references the formulas
refer to.

Thanks for thinking about this.

SteveC