Tasty Mango
New Member
- Joined
- May 12, 2016
- Messages
- 12
Hello all,
I'm attempting to make a formula that grabs from a list of parts and assemblies and generates a list of parts from the chosen assembly.
The mechanic is that all parts and assemblies have an adjacent column with their identifying serial (XXX-YYY) where XXX is the Assembly designation and YYY is the part designation.
For example a cup & ball toy would have the assembly code of 010-000, the ball would be 010-001, the string would be 010-002, the stick would be 010-003, etc).
I have a formula that lets me know the part count for a chosen assembly:
=IF($B5="","",(SUMIF(Page1!$D$7:$D$157,LEFT(VLOOKUP($B5,Page1!$C$7:$D$157,2,FALSE),3)&"*",Page1!$M$7:$M$140)-1)*IF(Page2'!$D$4="",0,Page2!$D$4))
This grabs the first 3 char from the serial (the assembly code) and counts only parts under that assembly family, and has worked rather well for me.
The parts list function:
{=IFERROR(INDEX(Page1!$C$7:$C$157 ,SMALL(IF(Page1!$D$7:D$157=LEFT(VLOOKUP(Page2!L4,Page1!$C$7:$D$157,2,FALSE)*3)&"*",-ROW(Page1!$C$7:$C$157)-ROW(Page1!$C$7)+1),ROWS(Page1!$C$7:$C7))),"")}
however has not worked at all.
Here's a breakdown of what I'm trying to do:
=IFERROR(INDEX(Page1!$C$7:$C$157 , [Checks the list of names for containing parts and assemblies]
SMALL(IF(Page1!$D$7:D$157=LEFT(VLOOKUP(Page2!L4,Page1!$C$7:$D$157,2,FALSE)*3)&"*", [Grabs the first 3 characters of the part (the assembly designation) and filters out any parts not part of the selected assembly]
ROW(Page1!$C$7:$C$157)-ROW(Page1!$C$7)+1),ROWS(Page1!$C$7:$C7))),"") [Generates list of qualifying parts]
Would any of you have a solution for this?
Thanks,
-TM
I'm attempting to make a formula that grabs from a list of parts and assemblies and generates a list of parts from the chosen assembly.
The mechanic is that all parts and assemblies have an adjacent column with their identifying serial (XXX-YYY) where XXX is the Assembly designation and YYY is the part designation.
For example a cup & ball toy would have the assembly code of 010-000, the ball would be 010-001, the string would be 010-002, the stick would be 010-003, etc).
I have a formula that lets me know the part count for a chosen assembly:
=IF($B5="","",(SUMIF(Page1!$D$7:$D$157,LEFT(VLOOKUP($B5,Page1!$C$7:$D$157,2,FALSE),3)&"*",Page1!$M$7:$M$140)-1)*IF(Page2'!$D$4="",0,Page2!$D$4))
This grabs the first 3 char from the serial (the assembly code) and counts only parts under that assembly family, and has worked rather well for me.
The parts list function:
{=IFERROR(INDEX(Page1!$C$7:$C$157 ,SMALL(IF(Page1!$D$7:D$157=LEFT(VLOOKUP(Page2!L4,Page1!$C$7:$D$157,2,FALSE)*3)&"*",-ROW(Page1!$C$7:$C$157)-ROW(Page1!$C$7)+1),ROWS(Page1!$C$7:$C7))),"")}
however has not worked at all.
Here's a breakdown of what I'm trying to do:
=IFERROR(INDEX(Page1!$C$7:$C$157 , [Checks the list of names for containing parts and assemblies]
SMALL(IF(Page1!$D$7:D$157=LEFT(VLOOKUP(Page2!L4,Page1!$C$7:$D$157,2,FALSE)*3)&"*", [Grabs the first 3 characters of the part (the assembly designation) and filters out any parts not part of the selected assembly]
ROW(Page1!$C$7:$C$157)-ROW(Page1!$C$7)+1),ROWS(Page1!$C$7:$C7))),"") [Generates list of qualifying parts]
Would any of you have a solution for this?
Thanks,
-TM
Last edited: