[Jon_JnP]

Hello all, looking for a little help with an excel function. I'm not even 100% sure WHICH function I should be trying to use, so I'll explain what I'm trying to do...

I've made two separate lists, one for part type and one for colors, and made pull down menus based on that data. What I'm trying to do, is make a third column auto populate with the actual part number based on the two selections. I have a list, of all the possible parts, colors and numbers on a separate tab, I can combine them if needed...

I would use Access, but not everyone here has it, and I need it to be accessible to everyone...

Any help would be much appriciated!

[JBeaucaire]

If you use the paperclip icon to attach your workbook so I can see how your reference data is laid out, this shouldn't be too hard.

[Jon_JnP]

I attached a sample of what I'm trying to do. I have the data to the right, keep in mind we actually have several models, with many more colors. I'm trying to make it so selecting the model and color, will auto populate the rest of the numbers.

Thanks for your help! Its definitely much appreciated!

[ScottGem]

Just a point here. Not sure what else your spreadsheet does, but what you are describing is more of a database function, not a spreadsheet function. You are talking about a relational database and, while Excel, has some database functionality, its is not really suitable for managing lots of data, especially relational data like you describe.

I would recommend considering moving your app to Access.

[Jon_JnP]

Thanks Scott, I know access would be much easier to do this, unfortunately not everyone here has access, and there are several people that will be using various versions of this. I really wish I could go that route but excel is my only option. If I can't make it work, everyone will just have to look up the numbers manually.

[ScottGem]

Thanks Scott, I know access would be much easier to do this, unfortunately not everyone here has access, and there are several people that will be using various versions of this. I really wish I could go that route but excel is my only option. If I can't make it work, everyone will just have to look up the numbers manually.

Not true. As long as someone in your organization has Access and can develop your application, that is all that is needed. For the people who need to USE the application, Access makes a runtime version available at no cost. That's how my company works. We have less than a dozen people who have Office Professional but 10x that many who use Access apps. Everyone who doesn't have Office Pro has the runtime installed and can use the apps that I develop.

If this data needs to be used by multiple people at the same time, if it contains standard tables of data that need to be consistent across users. If you need to maintain lots of data, then Excel is not going to work well for you. Rather than try to shoehorn the functionality into Excel, there is no reason, not to use Access.

[Jon_JnP]

Thanks again Scott, I tried doing it that way, but the boss says "Access is not user friendly". After playing with it a few more hours, I finally got it to work though using the VLookup function. Quite simple actually once I figured it out :)

Thanks again!

[ScottGem]

Thanks again Scott, I tried doing it that way, but the boss says "Access is not user friendly". After playin with it a few more hours, I finally got it to work though using the VLookup function. Quite simple actually once I figured it out :)

Thanks again!

Again, not true. While Access can be a bit difficult to setup an application, an app is as user friendly as the developer makes it. A user needs to know NOTHING about Access to use an Access app. You create a form that is user friendly and walks a user through the process to enter and view data.

Now, if there is no one in your organization capable of developing a user friendly Access app, that is one thing. But I still maintain that trying to use Excel to do what you are describing in using a hammer to drive a screw.