Totaling specific configuration selections that cross reference multiple fields (tricky)
I need assistance on a spreadsheet that has several sheets, macros and formulas that all talk to one another. Management would like me to figure out a way to total-up the total square footage per each type of board grade/flute/liner combo and then have the $/MSF calculated. The $/MSF (Q48:V48) is calculated by looking at the total MSF (Q47:V47) and referencing the cost on the ‘Price Matrix’ tab. The catch is, management would like to see a total cost for each type of board grade/flute/liner combo (columns F, G, H & O). This can either be displayed on the same spreadsheet or calculated on its own sheet. This means for each configuration of choices for these 4 columns I need it to total its own $/MSF for each one and list them all out. To make matters worse, depending on the selection for columns F, G, H & O (these are set choices in a dropdown list) there could be additional cost adders for the selections made (columns X – AC). And, these costs are only to be added to the total baseline for each configuration. For example, if I choose 250#, B, OYS & 2 this should give me an adder of $39.23 for this selection. However, if I have another part with the same configuration of “250#, B, OYS & 2” the adder of $39.23 should only be added to the total $/MSF one time.
I am not sure if should/can be done with formatting/formulas or if something will need to be coded. Any assistance would be much appreciated as I have met my limit skillset-wise.
I attached a copy of the file and will be more than grateful for any suggestions. Thank you in advance.
|