I have 2 worksheets in my workbook. The 1st worksheet is a datase of part
numbers and classification codes. It has about 30,000 part numbers and 100 or
more classification codes. The 2nd worksheet is where I enter part numbers to
get the classification code. I'm using a vlookup on sheet 2 to reference part
numbers on sheet 1. When I enter the part number, I also enter cost & weight,
and excel provides the correct classification code.

However, I need to summarize by classification the total cost & weight.

I have a table on sheet 2 that will do that, but I would like excel to
automatically populate the classification code. I was able to do this with
nested IF functions, but it stops at 7 or 8 variables.

In the column of classification codes, row 1 will always be row 1 in the
summary table. But the classification code in row 2 may or may not match row
1. Sometimes row 1 through 15 will be the same and row 16 will be different.
Or Rows 1-4 are the same, row 5 is different than rows 1-4, rows 6-10 are the
same as 5, and row 11 is the same as rows 1-4.

Is there a formula where excel can see a change in value and return that new
value?

Have you looked at SUMIF and COUNTIF ?

Basically:

=SUMIF(class_code_range,class_code,cost_range)

Hope this helps.

Pete

On Oct 2, 4:58*pm, Big Dave <Big
wrote:
I have 2 worksheets in my workbook. The 1st worksheet is a datase of part
numbers and classification codes. It has about 30,000 part numbers and 100 or
more classification codes. The 2nd worksheet is where I enter part numbers to
get the classification code. I'm using a vlookup on sheet 2 to reference part
numbers on sheet 1. When I enter the part number, I also enter cost & weight,
and excel provides the correct classification code.

However, I need to summarize by classification the total cost & weight.

I have a table on sheet 2 that will do that, but I would like excel to
automatically populate the classification code. I was able to do this with
nested IF functions, but it stops at 7 or 8 variables.

In the column of classification codes, row 1 will always be row 1 in the
summary table. But the classification code in row 2 may or may not match row
1. Sometimes row 1 through 15 will be the same and row 16 will be different.

Thanks, Pete.
I have the table set up to sum the costs and weights as soon as a
classification code is populated. What I am doing right now is a
copy-paste(value) from my entry form. It's just a little more complicated
when I have 10 or 11 classification codes to copy & paste.

It would be a lot easier if excel could look at that column and
automatically populate the classification codes.

"Pete_UK" wrote:

Have you looked at SUMIF and COUNTIF ?

Basically:

=SUMIF(class_code_range,class_code,cost_range)

Hope this helps.

Pete

On Oct 2, 4:58 pm, Big Dave <Big
wrote:
I have 2 worksheets in my workbook. The 1st worksheet is a datase of part
numbers and classification codes. It has about 30,000 part numbers and 100 or
more classification codes. The 2nd worksheet is where I enter part numbers to
get the classification code. I'm using a vlookup on sheet 2 to reference part
numbers on sheet 1. When I enter the part number, I also enter cost & weight,
and excel provides the correct classification code.

However, I need to summarize by classification the total cost & weight.

I have a table on sheet 2 that will do that, but I would like excel to
automatically populate the classification code. I was able to do this with
nested IF functions, but it stops at 7 or 8 variables.

In the column of classification codes, row 1 will always be row 1 in the
summary table. But the classification code in row 2 may or may not match row
1. Sometimes row 1 through 15 will be the same and row 16 will be different.
Or Rows 1-4 are the same, row 5 is different than rows 1-4, rows 6-10 are the
same as 5, and row 11 is the same as rows 1-4.

Is there a formula where excel can see a change in value and return that new
value?

You can always use pivot tables. Pivot your sheet 1, grab class cd as your
row field, then drop in cost and weight as data fields. Right-click on each
to ensure they are set to Sum, not count. Whenever new ones come in, you can
right-click on the pivot and hit refresh data. Can also set the pivot to
refresh on open in Table Options...

"Big Dave" wrote:

Thanks, Pete.
I have the table set up to sum the costs and weights as soon as a
classification code is populated. What I am doing right now is a
copy-paste(value) from my entry form. It's just a little more complicated
when I have 10 or 11 classification codes to copy & paste.

It would be a lot easier if excel could look at that column and
automatically populate the classification codes.

"Pete_UK" wrote:

Have you looked at SUMIF and COUNTIF ?

Basically:

=SUMIF(class_code_range,class_code,cost_range)

Hope this helps.

Pete

On Oct 2, 4:58 pm, Big Dave <Big
wrote:
I have 2 worksheets in my workbook. The 1st worksheet is a datase of part
numbers and classification codes. It has about 30,000 part numbers and 100 or
more classification codes. The 2nd worksheet is where I enter part numbers to
get the classification code. I'm using a vlookup on sheet 2 to reference part
numbers on sheet 1. When I enter the part number, I also enter cost & weight,
and excel provides the correct classification code.

However, I need to summarize by classification the total cost & weight.

I have a table on sheet 2 that will do that, but I would like excel to
automatically populate the classification code. I was able to do this with
nested IF functions, but it stops at 7 or 8 variables.

In the column of classification codes, row 1 will always be row 1 in the
summary table. But the classification code in row 2 may or may not match row
1. Sometimes row 1 through 15 will be the same and row 16 will be different.
Or Rows 1-4 are the same, row 5 is different than rows 1-4, rows 6-10 are the
same as 5, and row 11 is the same as rows 1-4.

Is there a formula where excel can see a change in value and return that new
value?