Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
A vlookup function for Access
- Thread starter henio
- Start date
bluedragon2
IS-IT--Management
Just a thought:
Why not use MS Query to extract the data you need from the Access Database's
Blue![[dragon]](/data/assets/smilies/dragon.gif "[dragon] [dragon]")
If I wasn't Blue, I would just be a Dragon...
Why not use MS Query to extract the data you need from the Access Database's
Blue
If I wasn't Blue, I would just be a Dragon...
You would not need to export the data to excel, (too many steps)...
Create a live link to the table or query from within excel,
From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
or follow the wizard from there (to select your mdb, table or query),I would recommend the MsQuery option.
Then return your data to excel.
When data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.
This is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option).
To make your query more dynamic...
you can add user input parameters by either bracketing the prompt in the criteria area of the msquery editor, or by assigning the parameter to a cell value in a sheet.
Create a live link to the table or query from within excel,
From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
or follow the wizard from there (to select your mdb, table or query),I would recommend the MsQuery option.
Then return your data to excel.
When data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.
This is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option).
To make your query more dynamic...
you can add user input parameters by either bracketing the prompt in the criteria area of the msquery editor, or by assigning the parameter to a cell value in a sheet.
- Thread starter
- #4
ETID and bluedragon2,
I already use MSQuery a lot, but perhaps there is something I don't know about it.
The scenario is as follows. I get a lot of part numbers sent to me in Excel. For each item, I need to retrieve additional data from an Access table which is very large. You imply that you can pass the values of a range of cells as input parameters to MSQuery. How do you do that? Also, how do you control where the retrieved data is placed in Excel? Obviously the returned data needs to aligned to the original cells. Also, how would you spot #N/As that you can get in a vlookup?
Cheers,
Henio
I already use MSQuery a lot, but perhaps there is something I don't know about it.
The scenario is as follows. I get a lot of part numbers sent to me in Excel. For each item, I need to retrieve additional data from an Access table which is very large. You imply that you can pass the values of a range of cells as input parameters to MSQuery. How do you do that? Also, how do you control where the retrieved data is placed in Excel? Obviously the returned data needs to aligned to the original cells. Also, how would you spot #N/As that you can get in a vlookup?
Cheers,
Henio
- Thread starter
- #6
Hi Etid,
it could be up to 5000. That's why I thought I might need a function which I could use like vlookup.
I have thought of importing the data into a new table, creating a query and exporting the results back to Excel, but this would leave me doing all the work :-(
Cheers,
Henio
it could be up to 5000. That's why I thought I might need a function which I could use like vlookup.
I have thought of importing the data into a new table, creating a query and exporting the results back to Excel, but this would leave me doing all the work :-(
Cheers,
Henio
One other way....
In access, you could link to the sheet with the part # list (assuming that the list will be on a sheet in excel and in col A) as though it were a table.
then build an access query joining that linked table to the other access tables of data....
then in another worksheet in the same workbook as the part # list, use msquery to return data based on that access query.
When excel users populate the list on the list numbers sheet....have some sort of event like a button click or a sheet activation event....trigger an Msquery refresh of the data.
In access, you could link to the sheet with the part # list (assuming that the list will be on a sheet in excel and in col A) as though it were a table.
then build an access query joining that linked table to the other access tables of data....
then in another worksheet in the same workbook as the part # list, use msquery to return data based on that access query.
When excel users populate the list on the list numbers sheet....have some sort of event like a button click or a sheet activation event....trigger an Msquery refresh of the data.
- Status
