Hi,

I've got an Access Database that is 128k rows. I dont want to pull it in to
XL as the file is too big, and is more than one Tab.

I would like to interrogate this table using Array formulae, Vlookup and
wanted to know how (if at all possible) i would write the location of the
source data, eg

=vlookup(A1,'Data1.mdb!Table',offset)

Thanks

John

Hi John,

I would like to interrogate this table using Array formulae, Vlookup and
wanted to know how (if at all possible) i would write the location of the
source data, eg

=vlookup(A1,'Data1.mdb!Table',offset)

What you describe cannot be done directly. But you can use an Access table
as the basis for a pivot table directly. Or you can set up a query to your
table and filter the data: data, get external data, new database query.

You can even create a parameter query and tie the parameter to a worksheet
cell in such a way that the query updates as soon as the cell's value
changes. I think this is what you'd need.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Jan,

Thanks for the reply. I think the Parameter Query could be what i'm after.
would you be able to give me an example of how i would write this please?

Thanks

John


"Jan Karel Pieterse" wrote:

Hi John,

I would like to interrogate this table using Array formulae, Vlookup and
wanted to know how (if at all possible) i would write the location of the
source data, eg

=vlookup(A1,'Data1.mdb!Table',offset)

What you describe cannot be done directly. But you can use an Access table
as the basis for a pivot table directly. Or you can set up a query to your
table and filter the data: data, get external data, new database query.

You can even create a parameter query and tie the parameter to a worksheet
cell in such a way that the query updates as soon as the cell's value
changes. I think this is what you'd need.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi John,

I think the Parameter Query could be what i'm after.
would you be able to give me an example of how i would write this please?

http://www.dicks-blog.com/archives/2...xcel-external-
data-queries/

(watch out for word wrap!)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com