I have the unique opportunity to streamline a process within our company. It's unique because our customer is complaining to us for not billing quick enough. The customer is right, we are about 3 months behind :confused:

I have an excel workbook. Within the first page of the workbook is a column containing part numbers, approximately 200. The next column is for clasifying the product, 10 seperate product clasifications. I want to enter the part number and have excel automatically populate the clasification column.

I understand how to do simple "yes, no" if statements. Can I program excel to handle this many options? I'm not getting it to work :frust:

I have the unique opportunity to streamline a process within our company. It's unique because our customer is complaining to us for not billing quick enough. The customer is right, we are about 3 months behind http://elsmar.com/Forums/images/smilies/confused.gif

I have an excel workbook. Within the first page of the workbook is a column containing part numbers, approximately 200. The next column is for clasifying the product, 10 seperate product clasifications. I want to enter the part number and have excel automatically populate the clasification column.

I understand how to do simple "yes, no" if statements. Can I program excel to handle this many options? I'm not getting it to work http://elsmar.com/Forums/images/smilies/frustrated.gif
I don't have any direct help for you, but I do have a source for some. The Microsoft newsgroups are an excellent source for expert peer-to-peer help with MS products. The group you want, (using a newsreader such as Outlook Express or Firefox) is microsoft.public.excel.programming. To access the group through a web-based interface, go here: http://support.microsoft.com/newsgroups/?pr=918 and click on "excel.programming" in the lefthand column.

I'm not sure if doing an auto filter will help but for me, we have many different "project" numbers and using the auto filter, I'm able to see all of the submissions for each client just by picking the project number. (Data - Autofilter)

Not sure if that would help but could make it a little easier until you find a sure fix.

Cathie

I have an excel workbook. Within the first page of the workbook is a column containing part numbers, approximately 200. The next column is for clasifying the product, 10 seperate product clasifications. I want to enter the part number and have excel automatically populate the clasification column.


Take a look at the "hlookup" and "vlookup" functions in Excel help. If you still have problems after that, I can set you up a little example.

I have the unique opportunity to streamline a process within our company. It's unique because our customer is complaining to us for not billing quick enough. The customer is right, we are about 3 months behind :confused:

I have an excel workbook. Within the first page of the workbook is a column containing part numbers, approximately 200. The next column is for clasifying the product, 10 seperate product clasifications. I want to enter the part number and have excel automatically populate the clasification column.

I understand how to do simple "yes, no" if statements. Can I program excel to handle this many options? I'm not getting it to work :frust:Mike, how does the part no. relate to the classification? Does one particular digit within the p/n identify the class. Can you give us an example?

Paste this into the cell that you want the value to appear in and change the cell references and values as needed.

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

Paste this into the cell that you want the value to appear in and change the cell references and values as needed.

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

mshell, thanks a bunch. That is exactly what I was looking for. My problem was those #@%$ (). I had one wrong and couldnt see it until I looked at your script!!!!!!!!!!!!!

:thanks: :thanx: :thanks:

You are welcome. Glad to be of assistance.

Another solution would be to copy the list of part numbers into column B of Sheet 2. Then enter 1 to 200 in column A Sheet 2. Then enter the classification of each part into column C, Sheet 2.

Then in column B, row 1, Sheet 1, enter =VLOOKUP(A1,Sheet2!$A$1:$C$200,3)
Then highlight the cell (B1) and drag down the rest of column B Sheet 1.

Enter the part number in column A, Sheet 1 and excel will do the rest!

Obviously your part numbers relate to the classification so the earlier posted solution is easier.

mshell has the process I was looking for. Soemthing I just discovered is they stop you at 7 variables. I have 270 unique products, something I discovered after a whole lot of potentially wasted typing. Anyone have an answer on how to use IF statements for more than 7?

Those that have responded with LOOKUP functions, I havent ignored your input. I may still end up using that function.

Try using access instead of excel.

Try using access instead of excel.
LOL, I KNOW EVEN LESS ABOUT ACCESS!

I think you need to consider using VBA for this.

I also need a little clarification on what you are wanting.

Are you wanting to look up the product classification based on the part number?

or are you entering a new part number and want the spreadsheet to classify the part based on the part number?

I need to start my own business and design interactive forms and databases.

I love that kind of stuff and it makes work so much easier and faster.

I have one customer that sent me a 24 page survey that requires hand written responses. I would almost rather recreate it and make it interactive than handwrite the responses (LOL).

entering a new part number and want the spreadsheet to classify the part based on the part number?

Above is what I need to do. I have a list of 270 products, each belonging to one of 11 families. When I enter the product into a row in column A, I want the same row in column B to automatically populate the family for that product entered into that row.

Hmmm, vlookup may be able to achieve what you want but i'm not 100% certain it can, need to have a think.

Really the best way to do it would be to swap it to an access database but thats not really an option. You can do it using VBA, if you have the skills, otherwise its going to be vlookup with the part of it called the "range lookup" being set to TRUE but I am almost 100% certain putting this to true will allow the possibility of the part being wrongly categorised to creep in.

Here is a little vlookup demo. Type a part number in the yellow cell, and it will look up A or B.

Anyone have an answer on how to use IF statements for more than 7?

When I have an extremely complicated function with lots of nested statements, I develop and test it in several columns. You could break up your formula and hide the intermediate stages, just showing the final result as your category column. That would help you get around the limit.

B.G. Wiehle

Here is a little vlookup demo. Type a part number in the yellow cell, and it will look up A or B.
Steve, this should work. Thank you for the demo. Seeing the demo allowed me to better understand what vlookup does. I'll play with it some more and follow-up with questions or issues if they arise.

Thanks,
Mike

you might want to try the Database Query option in Excel.