Cadillacmatt
New Member
- Joined
- Aug 23, 2012
- Messages
- 4
Hello all,
I work for an ice cream manufacturing plant, and I am designing a "Report" worksheet within a "Daily Plant
Analysis" workbook. This workbook allows users to enter various information about their day-to-day runs.
During the course of a day, each ice cream production line we have (total of 6) might run 2 or 3 different
products on that line. Each "run" of products comes associated with a Yield Variance, which compares the total finished product output to the amount of starting mix.
Here's what I need the "Report" worksheet to accomplish. I will allow selection of an ice cream product
(governed by Data Validation list), and when that product is selected, I need another cell to display the
Yield Variance for that particular product, IF it has been run and entered on the "Yield" worksheet. The Yield
worksheet contains the data about Yield Variance for whatever product was run that day. It has every single
ice cream flavor & its corresponding unique item code on its own row, and listed in the first two columns. The
team leader would enter yield under whatever flavor is appropriate (if a particular flavor was not run, then
the Yield column would have no value).
This is where I face my challenge. When the user selects a flavor from the drop down box, I need Excel to
look-up the value selected in the "Report" worksheet, do a search for that same item name in the "Yield"
worksheet, and once it finds a matching value on that sheet, it will count a certain number of cells to the
right to arrive at the "Yield" column for that particular flavor. It would then display this value in the cell
next to the flavor selected in the "Report" sheet.
This seems like it could be accomplished with a combination of VLOOKUP and COUNT or OFFSET possibly, but I'm really at a loss here. If anyone can provide insight on this I will be eternally grateful. Thank you!!
Here are some visual aids to helpfully clarify what I'm talking about - In the first picture, you can see the "Report" sheet I've designed. The drop down box is selected for "SQ - Vanilla". The Yield cell will display the proper value for yield for whatever product has been entered in the "Yield" sheet.
The second attachment is the "Yield" sheet. As you can see, each flavor has its own row, and in this instance, only Square Vanilla was run that day. Excluding the fact that the names are different between the Data Validation list and the Yield List (I can fix this) the formula would need to find this value based on what the user has entered in the previous sheet. The value is highlighted with a red arrow.
I work for an ice cream manufacturing plant, and I am designing a "Report" worksheet within a "Daily Plant
Analysis" workbook. This workbook allows users to enter various information about their day-to-day runs.
During the course of a day, each ice cream production line we have (total of 6) might run 2 or 3 different
products on that line. Each "run" of products comes associated with a Yield Variance, which compares the total finished product output to the amount of starting mix.
Here's what I need the "Report" worksheet to accomplish. I will allow selection of an ice cream product
(governed by Data Validation list), and when that product is selected, I need another cell to display the
Yield Variance for that particular product, IF it has been run and entered on the "Yield" worksheet. The Yield
worksheet contains the data about Yield Variance for whatever product was run that day. It has every single
ice cream flavor & its corresponding unique item code on its own row, and listed in the first two columns. The
team leader would enter yield under whatever flavor is appropriate (if a particular flavor was not run, then
the Yield column would have no value).
This is where I face my challenge. When the user selects a flavor from the drop down box, I need Excel to
look-up the value selected in the "Report" worksheet, do a search for that same item name in the "Yield"
worksheet, and once it finds a matching value on that sheet, it will count a certain number of cells to the
right to arrive at the "Yield" column for that particular flavor. It would then display this value in the cell
next to the flavor selected in the "Report" sheet.
This seems like it could be accomplished with a combination of VLOOKUP and COUNT or OFFSET possibly, but I'm really at a loss here. If anyone can provide insight on this I will be eternally grateful. Thank you!!
Here are some visual aids to helpfully clarify what I'm talking about - In the first picture, you can see the "Report" sheet I've designed. The drop down box is selected for "SQ - Vanilla". The Yield cell will display the proper value for yield for whatever product has been entered in the "Yield" sheet.
The second attachment is the "Yield" sheet. As you can see, each flavor has its own row, and in this instance, only Square Vanilla was run that day. Excluding the fact that the names are different between the Data Validation list and the Yield List (I can fix this) the formula would need to find this value based on what the user has entered in the previous sheet. The value is highlighted with a red arrow.
