Forum Discussion
Solved
Creating a material list from database and returning multiple values
Hello,
I'm trying to create a worksheet that will auto fill data as I type it , let me explain.
Basically I've imported an access database into a excel worksheet. Inside the same workbook , on a different sheet, Id like to begin typing say "3/8 bolts" and when I hit enter I want it to go look through the data base find "3/8 Bolts" and return the price and a couple other values for me. This would allow me to make quick list with pricing and I wouldn't have to look up the price constantly. It would be nice if it also created a drop down menu so when i started typing "3/8....." a menu would pop up and suggest "Bolt" "nuts" " washers".
Please Help
- What you're wanting to do is quite readily achieved. In fact, as is often true with Excel, there are several ways to get from point A to point Z. Which is most appropriate depends very much on a number of other aspects of the full situation.
My guess is that there are lots of types of "Things" as well as multiple "Sizes" and some Sizes would correspond to Screws but not to Spanners. Or whatever. So personally--as a layman who occasionally uses hardware but is by no means an expert--I'd start by entering "Bolt" and then have an adjoining column that has a drop-down menu with sizes that only pertain to Bolts. Same for "Nuts" and so on.
If in fact the total range of products and sizes (ALL possible combinations) is a very limited set, then you could create a single drop-down list. But my guess is that this is NOT the case.
Another crucial question: do you expect the fundamental database (including pricing) to be static, or will it change from month to month? Will new items be added, old ones deleted? Etc. The answer to this will also determine the best approach.
Once the part type and size are specified, it would be quite easy to use XLOOKUP--or one of the other functions that looks things up in a database--to retrieve the rest of the pricing data.
If the workbook you have is not proprietary, would it be possible to post a sample or a complete copy?
5 Replies
- What you're wanting to do is quite readily achieved. In fact, as is often true with Excel, there are several ways to get from point A to point Z. Which is most appropriate depends very much on a number of other aspects of the full situation.
My guess is that there are lots of types of "Things" as well as multiple "Sizes" and some Sizes would correspond to Screws but not to Spanners. Or whatever. So personally--as a layman who occasionally uses hardware but is by no means an expert--I'd start by entering "Bolt" and then have an adjoining column that has a drop-down menu with sizes that only pertain to Bolts. Same for "Nuts" and so on.
If in fact the total range of products and sizes (ALL possible combinations) is a very limited set, then you could create a single drop-down list. But my guess is that this is NOT the case.
Another crucial question: do you expect the fundamental database (including pricing) to be static, or will it change from month to month? Will new items be added, old ones deleted? Etc. The answer to this will also determine the best approach.
Once the part type and size are specified, it would be quite easy to use XLOOKUP--or one of the other functions that looks things up in a database--to retrieve the rest of the pricing data.
If the workbook you have is not proprietary, would it be possible to post a sample or a complete copy?mathetes wow, I really appreciate you taking the time to respond.
So yes, it will be changing often as we update prices and products. That is why I have created an access database which we will have on a local server so many users can keep the information up to date.
Attached is the very beginning of what I am striving for . There will be a lot more material but the big thing is no matter what line I begin typing a type of material it finds it and inputs the price and labor units associated with it.
- Unfortunately, the links to the Access database are disabled in the sample. So I can't do anything with it to test the connection with a database.
I haven't used Access in decades (I"m retired now); I find myself wondering whether it's possible to do all that you're wanting to IN Access. Perhaps not...
Anyway, I'll see what I can do with the Excel sheet...but it'll have to be later today or even tomorrow. Maybe somebody else will chime in, someone with experience connecting to Access.
