Copy the whole MS Access table to another worksheet in your workbook (can be done with copy/paste) and use vlookup-function.

The drawback of the method I suggested is that you have to redo the copy when you've got new parts or when prices change. If you've got multiple workbooks (say, 1 per job) it's easier to make a separate workbook to copy the data for MS Access to, and refer to that workbook in your vlookup-formulas. And, continuing in that line, it's still easier to make a macro in MS Access to export the table to that workbook than to copy and paste it. Then all you've got to do is run that macro in MS Access after each change of your materials table. That takes just a little bit of discipline, but as it's about your own money that shouldn't be too difficult.

The drawback of the method in previous paragraph is that the values in your Excel spreadsheet will change if you open it after an update in Access. So, I think you should copy/paste values on your estimates (to the same location, so over-writing your formulas) after you finished your estimates for a job. That way, the 'history' is saved.


The alternative to the copy (or export, which amounts to the same, but is just an easier way) would be to write a lot of VBA-code to access MS Access data in Excel, I'm afraid. And you wouldn't be able to 'fix' your estimates across change of prices without a lot more code or a more difficult data base design.

Hope this helps,


Kees