Ask Experts Questions for FREE Help !
Ask
    Jon_JnP's Avatar
    Jon_JnP Posts: 6, Reputation: 1
    New Member
     
    #1

    Jul 25, 2013, 06:08 AM
    Functions in Excel 2007
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Jul 27, 2013, 09:19 AM
    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's Avatar
    Jon_JnP Posts: 6, Reputation: 1
    New Member
     
    #3

    Jul 31, 2013, 04:50 AM
    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!
    Attached Files
  1. File Type: xls Test.xls (16.0 KB, 133 views)
  2. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Jul 31, 2013, 05:56 AM
    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's Avatar
    Jon_JnP Posts: 6, Reputation: 1
    New Member
     
    #5

    Jul 31, 2013, 06:20 AM
    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Jul 31, 2013, 07:48 AM
    Quote Originally Posted by Jon_JnP View Post
    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's Avatar
    Jon_JnP Posts: 6, Reputation: 1
    New Member
     
    #7

    Jul 31, 2013, 12:14 PM
    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #8

    Jul 31, 2013, 12:30 PM
    Quote Originally Posted by Jon_JnP View Post
    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.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Selected functions not working in excel sheet [ 2 Answers ]

Sir, I find that during certain times the functions in excel sheet, for example. delete, copy, paste, insert cut cells etc were not working in my excel sheet. I don't know how many other functions are not working like this. But as I close the worksheet and reopen it starts working. What may...

2007 excel [ 2 Answers ]

I've progressed from 2003 to 2007 office. I am preparing a report that requires entered data in three adjacent columns. To condense the visual report I want the entire column on one sheet of paper. I've searched the ribbons only locating the page break. In excel 2007 how can I break the column at...

How to count how many of each in excel 2007 [ 1 Answers ]

I need to count how many of each item is contained in a column of data (for instance, 4000 rows of data, 100 different items, but multiples of each item - how many Apples, Oranges, Bananas, etc. of each?). How do I do that in Excel 2007?

Excel 2007 to have excel 2003 look? [ 6 Answers ]

Hey I have Microsoft Excel 2007, from my course at college I have been given instructions to do a task. However these instructions are for excel 2003. How do I change my excel 07 to look like excel 03 so that it is easier. I know there is a way, help greatly appreicated thanks.


View more questions Search