Hi all excelers!
I have a calculation worksheet with the following structure:
B10 has a Data Validation list (source from sheet named DATA) and fills out C10, F10 and I10 with VLOOKUP formulas // C10 is =IFERROR(VLOOKUP(B10,DATA!$AD$2:$AO$11,10,), 0)] // (same for F10 and I10 except the index is 11 and 12 respectively DATA!$AD$2:$AO$11,11) and DATA!$AD$2:$AO$11,12)
Whereas every entry in my B10 Data Validation list has a certain value in the same VLOOKUP table, the values that are displayed in C10, F10 and I10 do not have values attached to them, because they are part of a value found by VLOOKUP.
Problem:
I now need a solution to be able to substract C10's value and add a different value instead of it, which should be somehow possible to select from a drop down menu, preferrably a Data Validation list in C10 (but so far I haven't been able to insert a Data Validation list in C10 without losing the VLOOKUP formula already there. Same for F10 and I10, need to be able to replace them.
I'm pretty sure everyone is confused by now, about what I need to do.
So here's a different way of explaining it...
I'm trying to make a table to assemble a 3-layered product where each layer is a certain material. Most of these products (about 95%) are standard assembly types so they have product codes like 2K4; 2K3+4sel; 3K5 etc. There's exactly 10 of these standard product codes which are selectable from a dropdown menu (the Data Validation list in B10). If a product code is selected, a VLOOKUP formula fills out cells C10, F10 and I10 with the layer material automatically. But when someone orders a custom product that is based on the standard product except with one or two layers replaced with a custom layer, the table must recalculate the price of the product by substracting the price of layer 1 or layer 2 or layer 3 and adding the custom layer's value instead. There are 45 options of custom layers, which all have a code (33.1, 33.1OP, 4AP, 4FKAR etc) and a certain price by m2 (square meter). What I would ideally like to do, is assign values to them and stick them in a list with Data Validation, one in each (C10, F10 and I10). Problem is I can't assign a default layer price to be part of a standard product, because they are calculated in different ways, ie a standard layer is not part of the whole product price and doesn't have a fixed value. But in the event of a custom layer being ordered, a certain price (varies for each standard type), is substracted from the product's price and an additional price is added for the custom layer.
Really giving me a headache and there's nothing I can do to change the sales politics in the company, it's just the way it is.
I have a calculation worksheet with the following structure:
B10 has a Data Validation list (source from sheet named DATA) and fills out C10, F10 and I10 with VLOOKUP formulas // C10 is =IFERROR(VLOOKUP(B10,DATA!$AD$2:$AO$11,10,), 0)] // (same for F10 and I10 except the index is 11 and 12 respectively DATA!$AD$2:$AO$11,11) and DATA!$AD$2:$AO$11,12)
Whereas every entry in my B10 Data Validation list has a certain value in the same VLOOKUP table, the values that are displayed in C10, F10 and I10 do not have values attached to them, because they are part of a value found by VLOOKUP.
Problem:
I now need a solution to be able to substract C10's value and add a different value instead of it, which should be somehow possible to select from a drop down menu, preferrably a Data Validation list in C10 (but so far I haven't been able to insert a Data Validation list in C10 without losing the VLOOKUP formula already there. Same for F10 and I10, need to be able to replace them.
I'm pretty sure everyone is confused by now, about what I need to do.
So here's a different way of explaining it...
I'm trying to make a table to assemble a 3-layered product where each layer is a certain material. Most of these products (about 95%) are standard assembly types so they have product codes like 2K4; 2K3+4sel; 3K5 etc. There's exactly 10 of these standard product codes which are selectable from a dropdown menu (the Data Validation list in B10). If a product code is selected, a VLOOKUP formula fills out cells C10, F10 and I10 with the layer material automatically. But when someone orders a custom product that is based on the standard product except with one or two layers replaced with a custom layer, the table must recalculate the price of the product by substracting the price of layer 1 or layer 2 or layer 3 and adding the custom layer's value instead. There are 45 options of custom layers, which all have a code (33.1, 33.1OP, 4AP, 4FKAR etc) and a certain price by m2 (square meter). What I would ideally like to do, is assign values to them and stick them in a list with Data Validation, one in each (C10, F10 and I10). Problem is I can't assign a default layer price to be part of a standard product, because they are calculated in different ways, ie a standard layer is not part of the whole product price and doesn't have a fixed value. But in the event of a custom layer being ordered, a certain price (varies for each standard type), is substracted from the product's price and an additional price is added for the custom layer.
Really giving me a headache and there's nothing I can do to change the sales politics in the company, it's just the way it is.
