Part Number Configurator

[electromatic]

Good Day all,

Hopefully I can phrase my question properly!! I'm working on building a Part Number configurator using Validation Lists and then concatenating the final string into a separate cell. That part is working correctly but what I am wanting to add is a final price value to the full part number string from the different selections. Not all portions of the PN carry a value only a few, but I'm stuck in regards to which method I would use that would give me flexibility when it came to time to adjust those prices. I've attached my workbook so it can be evaluated. Sheet 1 contains the pricing I am pulling from.


Thanks in advance for any assistance.

[Sam Capricci]

perhaps you could point out where the information is you want combined and where it is coming from and what the end result looks like because it is a lot of information on the three tabs with no real explanation.

[electromatic]

I have data validation set up in the cells that are filled either blue or purple. Those cells concatenate to the top where the EMPD3 numbers are showing up. That part works fine. I'm trying to add pricing into C19 for the main build (blue box). The prices for the options for the main build are on sheet 1 column I. Column H has the option (i.e. H14 is option 1 which correlates to option 1 (B23) on the configurator sheet. Sorry if that doesn't make sense

[JeteMc]

Hello electromatic and Welcome to Excel Forum.
This is a somewhat "low-tech" proposal which may provide an idea as to how your goal might be accomplished.
Note that I didn't see the BREAKER MONITORING (PROFINET/ETHERNET), POWER MONITORING (PROFINET/ETHERNET) or CABLE TRAY options listed in column H.
Populate D22:D42 on the configurator sheet using: =IF(A22="","",INDEX(Sheet1!I14:I66,MATCH(A22,Sheet1!H14:H66,0)))
Note that for aesthetic purposes the font could be set to white so that values are hidden.
Populate cell C19 using: =SUM(D22:D42)
Let us know if you have any questions.

[electromatic]

Thank you so much! That works very well. The only issue I see is that when I select the X option under transformer selection the price shows as #NA. Any tips?

[JeteMc]

Modify the formula in cell D22 to read: =IF(OR(A22="x",A22=""),"",INDEX(Sheet1!I14:I66,MATCH(A22,Sheet1!H14:H66,0)))
Double click the fill handle to have the formula copy down to D42.
Let us know if you have any questions.