Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 26
Default vlookup from Access db

Hey there,

I reckon the subject asks it all, but to explain further:

I have barcodes in Excel in a column. Next to each I need to put a sales
volume, which must be "vlooked"-up from an Access db. Is this possible?

E.G. in Excel

BarCode Volume
123456 20.00 (formula)

In Access

BarCode Volume
123456 20.00

Thank you for any help!
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default vlookup from Access db

If the Access table is not large then I would use Data External Data
New Database Query to bring the table into Excel from Access.

(or equivalent; the naming has changed with different versions of Excel
- it always helps us to help you if you specify)

You can then do the VLOOKUP in Excel referencing the table.

Alternatively, if you have a large table but only need a small number
of lookups, you could make each lookup be a Query using the barcode as
a parameter.

Finally, if you have a very large table and quite a lot of lookups then
it might be better to use a macro to navigate a recordset based on the
Access table, and populate the Volume column.

If you need help with your chosen route, come back

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 26
Default vlookup from Access db

Hi Bill, thank you for your response.

I have many sheets with many lookups to many access tables (all within a
single db though), so I guess the last option would be best... can you
possible elaborate a bit more on this?

Thank you again!
W

"Bill Manville" wrote:

If the Access table is not large then I would use Data External Data
New Database Query to bring the table into Excel from Access.

(or equivalent; the naming has changed with different versions of Excel
- it always helps us to help you if you specify)

You can then do the VLOOKUP in Excel referencing the table.

Alternatively, if you have a large table but only need a small number
of lookups, you could make each lookup be a Query using the barcode as
a parameter.

Finally, if you have a very large table and quite a lot of lookups then
it might be better to use a macro to navigate a recordset based on the
Access table, and populate the Volume column.

If you need help with your chosen route, come back

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default vlookup from Access db

Many sheets with many lookups to many tables in a database suggests to
me that the design of your application may not be ideal for Excel, and
I would hesitate to promote the recordset idea in this case.

Try the QueryTable approach and see how you get on with it.

Have you considered doing it all in Access?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VLOOKUP when referencing data from Access asmithbcat Excel Worksheet Functions 2 February 13th 08 03:55 AM
VLOOKUP with restricted access rights DaveO[_2_] Excel Worksheet Functions 2 August 6th 07 08:20 PM
VLOOKUP from Access Tables Mraggie Excel Discussion (Misc queries) 1 August 9th 06 09:33 PM
VLOOKUP from Access Tables Mraggie Excel Worksheet Functions 0 August 9th 06 08:36 PM
vlookup and microsoft access finster26 Excel Worksheet Functions 0 January 6th 06 06:17 PM


All times are GMT +1. The time now is 09:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"