J
John Colling
Sorry this ones a bit long.
Background:
I have spreadsheet which contains a sheet with all the raw data and another
for the summary sheet.
The summary sheet uses a vlookup to get the data from the raw data by using
the "product" as a key driver.
I have created a "unqiue Ref" by using the "product" and "Operation"
combined, this returns all lines(rows) associated with the operation of a
product.
Extract in excel- B1 contains the "Product"
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F COLUMN G
ROW1 "Cake"
ROW2 Unqiue Ref Product Operation Description Cost Operation
Description
ROW3 Cake 1 cake 1 Setup 1 10 Operation 1
ROW4 Cake 2 cake 2 Operation 1 150 Operation 1
ROW5 Cake 3 cake 3 Material 1 10
ROW6 Cake 4 cake 4 Material 2 20
ROW7 Cake 5 cake 5 Material 3 30
ROW8 Cake 6 cake 6 Material 4 40
ROW9 Cake 7 cake 7 Setup 2 10 Operation 2
ROW10 Cake 8 cake 8 Operation 2 200 Operation 2
ROW11 Cake 9 cake 9 Setup 3 10 Operation 3
ROW12 Cake 10 cake 10 Operation 3 300 Operation 3
The problem that I have is that, each product has varying rows, some are
only 10 rows of data whilst others can be up to 50 lines, operations/
material vary on products and the order changes as some require operation 3
before operation 2. Just to throw another twist for example operation 2 has
different descriptions , i.e. oven 1, oven 2;
This makes it difficult to use a set of fixed descriptions for a "sumif
function" especially when operations don't follow that order necessarily.
What I'm trying to get to is the following summary to the left of the detail
(as above) is a summary of the operation step cost and a Cumulative cost
Operation
Description Total Cumulative
Material 100 100
Operation 1 160 260
Operation 2 210 470
Operation 3 310 780
I don't know what to do next, Hope the above makes sense????
Many thanks in advance
JC
Background:
I have spreadsheet which contains a sheet with all the raw data and another
for the summary sheet.
The summary sheet uses a vlookup to get the data from the raw data by using
the "product" as a key driver.
I have created a "unqiue Ref" by using the "product" and "Operation"
combined, this returns all lines(rows) associated with the operation of a
product.
Extract in excel- B1 contains the "Product"
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F COLUMN G
ROW1 "Cake"
ROW2 Unqiue Ref Product Operation Description Cost Operation
Description
ROW3 Cake 1 cake 1 Setup 1 10 Operation 1
ROW4 Cake 2 cake 2 Operation 1 150 Operation 1
ROW5 Cake 3 cake 3 Material 1 10
ROW6 Cake 4 cake 4 Material 2 20
ROW7 Cake 5 cake 5 Material 3 30
ROW8 Cake 6 cake 6 Material 4 40
ROW9 Cake 7 cake 7 Setup 2 10 Operation 2
ROW10 Cake 8 cake 8 Operation 2 200 Operation 2
ROW11 Cake 9 cake 9 Setup 3 10 Operation 3
ROW12 Cake 10 cake 10 Operation 3 300 Operation 3
The problem that I have is that, each product has varying rows, some are
only 10 rows of data whilst others can be up to 50 lines, operations/
material vary on products and the order changes as some require operation 3
before operation 2. Just to throw another twist for example operation 2 has
different descriptions , i.e. oven 1, oven 2;
This makes it difficult to use a set of fixed descriptions for a "sumif
function" especially when operations don't follow that order necessarily.
What I'm trying to get to is the following summary to the left of the detail
(as above) is a summary of the operation step cost and a Cumulative cost
Operation
Description Total Cumulative
Material 100 100
Operation 1 160 260
Operation 2 210 470
Operation 3 310 780
I don't know what to do next, Hope the above makes sense????
Many thanks in advance
JC