Looking to find the correct formulas / Open to Ideas

kmclean90 · Jan 15, 2024

Hi all,

I am trying to build a part number generator based on certain characteristics of what's required, and I am not even remotely sure how to achieve this. My excel skills are definitely not this advanced! Any help here would be really appreciated!

I will upload pictures I am working on with the data in place, below a description of what I am trying to do.

WHAT I AM TRYING TO DO:

When you open the PANEL BUILDER PICTURE, the parameters from the "PANEL BUILDER" sheet (highlighted in yellow, each column is a drop-down list) are selected, I need this combination of variables to pull data from the "PART NUMBER DATA" sheet (2nd picture) into the three different columns (on "PANEL BUILDER SHEET") A10 (Box), C10 (Interior), and E10 (Trim).

For example (I will save the uploaded file this way), The seven variables in yellow you will see upon opening the file will read from left to right, ""NO", "N/A", "3PH" "120/208V", "225A", "42CC", "AL".

With this specific combination, I need cell A10 (all on "PANEL BUILDER SHEET") to isolate "EZB2042RC" in the Box column, cell C10 "P1aL4A2-42" in the Interior column, and cell E10 "EZT2042 (S or F)" in the Trim Column.

Overall, the goal is to have the data being pulled from the "PART NUMBER DATA" sheet change in the columns A10, C10, and E10 on the "PANEL BUILDER "sheet as the variables in the yellow drop-down lists change.

Another example would be, if we change the variable to "NO", N/A", "3PH, "120/208V", "225A", "60CC", "AL" then column A10 should pull "EZB2054RC, C10 should pull "P1aL4A2-60" and E10 should pull "EZT2054RC (S or F)"

This is a bit long winded but that's what I'm trying to do! I hope there's some brilliant mind out there who is willing to help me with this!

Thank you for your time.

Tupe77 · Jan 16, 2024

I believe we need more information about the logic of selection. In the first example, 42CC seems to be the dominant value, i.e. 42 appears at the end of all selections. In the second example, 60CC produces a value in column E that ends with 60, but columns a and g get values ending in 54.

KRice · Jan 17, 2024

@kmclean90, as @Tupe77 has suggested, you have omitted a lot of information necessary to help you. The manufacturers' model naming convention follows a system, and you have not described that system, so based on the information you've provided, there is no way to help. I made a quick search and found an Eaton product catalog here:

https://www.eaton.com/content/dam/eaton/products/low-voltage-power-distribution-controls-systems/panelboards/eaton-panelboards-lighting-controls-product-guide-ca014005en-ca.PDF

And on pages 19-20 (of the pdf) there are several tables that seem to provide the information necessary for converting your inputs into product model numbers. This information should have been offered or described.

I believe you have several options. One is to construct the Interior Part number based on your inputs. Another option is to use your inputs to perform a lookup in a reference table that you maintain somewhere in your workbook. I'll demonstrate both in the example below. The Interior part is constructed based on the drop down selections using the model-name mask described by the manufacturer at the bottom of page 19. However, I caution that you have not provided sufficient information for this to work reliably. You would need to provide details about all possible drop down options so the formula could account for them correctly. The Box and Trim parts use two of your inputs to perform a lookup based on referencing a table generated from Table 32 on page 20. Even that lookup may not be reliable, as the table includes sections for:

Main Lugs Only Or Main Lugs With Sub-Feed Lugs,
Main Lugs With Through-Feed Lugs,
Main Lugs With Surge Protection Device

From what I can determine, all of these options use the same Box and Trim for some combination of Main Ampere (Maximum) and Number of Branch Circuits, but I do not know if that is universally true, so your inputs should include some indication about which of these three types should be used. I may make better sense to use a different type of lookup (rather than SWITCH) in the constructed part formula, but it would be good to understand what your possible inputs for each dropdown option look like. Then a formula might use INDEX/MATCH or XLOOKUP...I don't know enough yet.

MrExcel_20240112.xlsx

A

B

C

D

E

F

G

1

2

MLO OR MAIN BREAKER REQUIRED?

MAIN BREAKER SIZE?

1PH OR 3PH

VOLTAGE

BUS AMPERAGE

CIRCUITS

BUS MATERIAL

3

NO

N/A

3PH

120/208V

225A

42CC

AL

4

5

6

7

8

9

BOX

INTERIOR

TRIM

10

EZB2042RC

P1aL4A2-42

EZT2042 S or F

11

Sheet6

Cell Formulas
Range	Formula
A10	A10	=INDEX(FILTER(tbl32_p20,(ISNUMBER(SEARCH(LEFT(E3,3),tbl32_p20[Main Ampere (Maximum)])))*(tbl32_p20[Number Branch Circuit]=LEFT(F3,2))),1,4)
C10	C10	=TEXTJOIN(,,"P1a",SWITCH(A3,"No","L"), SWITCH(C3,"1PH",1,"3PH3",3,"3PH",4,""), SWITCH(G3,"AL","A","CU","C",""),SWITCH(E3,"100A",1,"225A",2,"400A",4,""),"-", SWITCH(F3,"18CC",18,"24CC",24,"30CC",30,"42CC",42,"60CC",60,"72CC",72,"84CC",84,""))
E10	E10	=INDEX(FILTER(tbl32_p20,(ISNUMBER(SEARCH(LEFT(E3,3),tbl32_p20[Main Ampere (Maximum)])))*(tbl32_p20[Number Branch Circuit]=LEFT(F3,2))),1,5)

Cells with Data Validation
Cell	Allow	Criteria
G3	List	AL,CU
E3	List	100A,225A,400A

MrExcel_20240112.xlsx

A

B

C

D

E

3

Type

Main Ampere (Maximum)

Number Branch Circuit

Box Catalogue Number

Trim Catalogue Number

4