M
Mick Henn
Hi All,
This might appear to be a long winded question and probably has quite a
simple answer.
I’m trying to create a template in excel for the creation of BOMs (bills of
materials). It’s essentially a list of components partnumbers which belong
are built under a top level SKU number. Beside each BOM I have tried to build
a number of formulae which will do a sanity check on the contents to ensure
they belong to the partnumber which they are being matched to. This part has
been simple, in my work book there are 3 sheets, one with the BOMs on it,
another with an extract from of component attributes and another with
toplevel attributes.
The functions which am using do a Vlookup of the component partnumber in
it’s extract sheet, check a particular column, then vlookup the top level
partnumber in it’s extract sheet and check the same column. And IF the 2
values are equal the formula returns a null but if not, returns text – “Check
Attributesâ€.
However, some components are common to every BOM and as such cannot have
attributes which are connected to each toplevel partnumber. My idea was to
create a table of all generic components in a separate (hidden) worksheet,
and for the function check this list first before performing any of the above
checks. That way the formula would first check if the component was generic,
and if not check if it’s attributes were suitable for the top level SKU.
Below is the formula I’ve created do date, it checks if the component is
generic and if it is it returns “Generic†(as I wanted) however, if the
component is not generic I receive a “#N/A†error.
Any input you can provide would be greatly appreciated and apologies again
for the verbose.
=IF(VLOOKUP(COMPONENT PARTUMBER,GENERIC COMPONENT LIST,1,FALSE)=COMPONENT
PARTNUMBER,"Generic",IF((VLOOKUP(COMPONENT PARTNUMBER,COMPONENT EXTRACT
SHEET,3,FALSE))=VLOOKUP(TOP LEVEL SKU NUMBER,TOP LEVEL SKU EXTRACT
,3,FALSE),â€â€,â€Check Attributesâ€))
This might appear to be a long winded question and probably has quite a
simple answer.
I’m trying to create a template in excel for the creation of BOMs (bills of
materials). It’s essentially a list of components partnumbers which belong
are built under a top level SKU number. Beside each BOM I have tried to build
a number of formulae which will do a sanity check on the contents to ensure
they belong to the partnumber which they are being matched to. This part has
been simple, in my work book there are 3 sheets, one with the BOMs on it,
another with an extract from of component attributes and another with
toplevel attributes.
The functions which am using do a Vlookup of the component partnumber in
it’s extract sheet, check a particular column, then vlookup the top level
partnumber in it’s extract sheet and check the same column. And IF the 2
values are equal the formula returns a null but if not, returns text – “Check
Attributesâ€.
However, some components are common to every BOM and as such cannot have
attributes which are connected to each toplevel partnumber. My idea was to
create a table of all generic components in a separate (hidden) worksheet,
and for the function check this list first before performing any of the above
checks. That way the formula would first check if the component was generic,
and if not check if it’s attributes were suitable for the top level SKU.
Below is the formula I’ve created do date, it checks if the component is
generic and if it is it returns “Generic†(as I wanted) however, if the
component is not generic I receive a “#N/A†error.
Any input you can provide would be greatly appreciated and apologies again
for the verbose.
=IF(VLOOKUP(COMPONENT PARTUMBER,GENERIC COMPONENT LIST,1,FALSE)=COMPONENT
PARTNUMBER,"Generic",IF((VLOOKUP(COMPONENT PARTNUMBER,COMPONENT EXTRACT
SHEET,3,FALSE))=VLOOKUP(TOP LEVEL SKU NUMBER,TOP LEVEL SKU EXTRACT
,3,FALSE),â€â€,â€Check Attributesâ€))