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 , ...
- 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?
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?
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.- Thank you for your help.
I was actually able to get it to search the other sheet and give me the values I need.
Now I just need a drop down menu when I start to type that shows me all the available optionsThis may not exactly what you had in mind, but it is what I had in mind. It's a set of drop down lists, with the second one dependent on what is selected in the first. This is just an example, and there are some comments in meant to show how it can work. It does need a new version of Excel.