Hello,

Am new to all this but this is what am trying to do

Sheet 1 - list of retail products

Sheet 2 -another list of Retail products but they have children
(materials) these are figures percentages required for the Retail
Product

Sheet 3 - this is doing a lookup against the Retail product with the
materials required against them =VLOOKUP(A2,BOM!$A$2:$D$36785,2)

Sheet 1 - Beans on toast

Sheet 2 - Beans on toast - Contain beans 40%
Beans on toast - Contains Toast 55%
Beans on toast - Contains butter 5%

Sheet 3 - Retail Product ING1 ING2 ING3

But if I know there is a second child to the parent it will still show
the first material.
How do I get the vlookup to look and the next material for the retail
product and repeat this process in the next column as there could be
up to 10 items without duplicating the any of the other materials for
that Retail Product

I know this is probably simple but am stuck

Here's a link to a Microsoft Access application for BOMs. You may find doing
this in a database is much more manageable than in a spreadsheet.

http://www.mvps.org/access/downloads/bom.zip

There are also plenty of discussions on the SQL Server newsgroups about bill
of materials


"sabbathnut" wrote:

Hello,

Am new to all this but this is what am trying to do

Sheet 1 - list of retail products

Sheet 2 -another list of Retail products but they have children
(materials) these are figures percentages required for the Retail
Product

Sheet 3 - this is doing a lookup against the Retail product with the
materials required against them =VLOOKUP(A2,BOM!$A$2:$D$36785,2)

Sheet 1 - Beans on toast

Sheet 2 - Beans on toast - Contain beans 40%
Beans on toast - Contains Toast 55%
Beans on toast - Contains butter 5%

Sheet 3 - Retail Product ING1 ING2 ING3

But if I know there is a second child to the parent it will still show
the first material.
How do I get the vlookup to look and the next material for the retail
product and repeat this process in the next column as there could be
up to 10 items without duplicating the any of the other materials for
that Retail Product

I know this is probably simple but am stuck

On 26 Feb, 15:15, Duke Carey
wrote:
Here's a link to a Microsoft Access application for BOMs. You may find doing
this in a database is much more manageable than in a spreadsheet.

http://www.mvps.org/access/downloads/bom.zip

There are also plenty of discussions on the SQL Server newsgroups about bill
of materials



"sabbathnut" wrote:
Hello,

Am new to all this but this is what am trying to do

Sheet 1 - list of retail products

Sheet 2 -another list of Retail products but they have children
(materials) these are figures percentages required for the Retail
Product

Sheet 3 - this is doing a lookup against the Retail product with the
materials required against them =VLOOKUP(A2,BOM!$A$2:$D$36785,2)

Sheet 1 - Beans on toast

Sheet 2 - Beans on toast - Contain beans 40%
Beans on toast - Contains Toast 55%
Beans on toast - Contains butter 5%

Sheet 3 - Retail Product ING1 ING2 ING3

But if I know there is a second child to the parent it will still show
the first material.
How do I get the vlookup to look and the next material for the retail
product and repeat this process in the next column as there could be
up to 10 items without duplicating the any of the other materials for
that Retail Product

I know this is probably simple but am stuck- Hide quoted text -

- Show quoted text -

I dont have Access am afraid

If you're set on using Excel, you can use a combination of COUNTIF (to count
how many children there are per item) , MATCH, and OFFSET to populate your
lists. If you have some more specifics on cell references of ranges where
your data is located I could give yuo a specific example.

"Duke Carey" wrote:

Here's a link to a Microsoft Access application for BOMs. You may find doing
this in a database is much more manageable than in a spreadsheet.

http://www.mvps.org/access/downloads/bom.zip

There are also plenty of discussions on the SQL Server newsgroups about bill
of materials


"sabbathnut" wrote:

Hello,

Am new to all this but this is what am trying to do

Sheet 1 - list of retail products

Sheet 2 -another list of Retail products but they have children
(materials) these are figures percentages required for the Retail
Product

Sheet 3 - this is doing a lookup against the Retail product with the
materials required against them =VLOOKUP(A2,BOM!$A$2:$D$36785,2)

Sheet 1 - Beans on toast

Sheet 2 - Beans on toast - Contain beans 40%
Beans on toast - Contains Toast 55%
Beans on toast - Contains butter 5%

Sheet 3 - Retail Product ING1 ING2 ING3

But if I know there is a second child to the parent it will still show
the first material.
How do I get the vlookup to look and the next material for the retail
product and repeat this process in the next column as there could be
up to 10 items without duplicating the any of the other materials for
that Retail Product

I know this is probably simple but am stuck

On 26 Feb, 16:20, Dave Breitenbach
wrote:
If you're set on using Excel, you can use a combination of COUNTIF (to count
how many children there are per item) , MATCH, and OFFSET to populate your
lists. If you have some more specifics on cell references of ranges where
your data is located I could give yuo a specific example.



"Duke Carey" wrote:
Here's a link to a Microsoft Access application for BOMs. You may find doing
this in a database is much more manageable than in a spreadsheet.

http://www.mvps.org/access/downloads/bom.zip

There are also plenty of discussions on the SQL Server newsgroups about bill
of materials

"sabbathnut" wrote:

Hello,

Am new to all this but this is what am trying to do

Sheet 1 - list of retail products

Sheet 2 -another list of Retail products but they have children
(materials) these are figures percentages required for the Retail
Product

Sheet 3 - this is doing a lookup against the Retail product with the
materials required against them =VLOOKUP(A2,BOM!$A$2:$D$36785,2)

Sheet 1 - Beans on toast

Sheet 2 - Beans on toast - Contain beans 40%
Beans on toast - Contains Toast 55%
Beans on toast - Contains butter 5%

Sheet 3 - Retail Product ING1 ING2 ING3

But if I know there is a second child to the parent it will still show
the first material.
How do I get thevlookupto look and the next material for the retail
product and repeat this process in the next column as there could be
up to 10 items without duplicating the any of the other materials for
that Retail Product

I know this is probably simple but am stuck- Hide quoted text -

- Show quoted text -

in worksheet BOM A1 holds the parent code A2 holds the child
in ORDERS sheet a1 holds the parent code i require a vlookup in c1 e1
g1 i1 k1 etc...but each vlookup knows not to display a child code if
its displayed in the previous cells

Dear Sirs,


How can I create Bill Of Material for assembly line?!

Supplier item# cost
A XX $2.00
B XX $3.00
C XX $4.00
A YY $5.00
A ZZ $3.00
C YY $2.00
C ZZ $4.00
B YY $6.00
B ZZ $7.00

PART# XYXY =
item A buy it from Supplier XX +
item B, from supplier YY +
item C, from supplier ZZ


--
Aaron Mandour


"sabbathnut" wrote:

Hello,

Am new to all this but this is what am trying to do

Sheet 1 - list of retail products

Sheet 2 -another list of Retail products but they have children
(materials) these are figures percentages required for the Retail
Product

Sheet 3 - this is doing a lookup against the Retail product with the
materials required against them =VLOOKUP(A2,BOM!$A$2:$D$36785,2)

Sheet 1 - Beans on toast

Sheet 2 - Beans on toast - Contain beans 40%
Beans on toast - Contains Toast 55%
Beans on toast - Contains butter 5%

Sheet 3 - Retail Product ING1 ING2 ING3

But if I know there is a second child to the parent it will still show
the first material.
How do I get the vlookup to look and the next material for the retail
product and repeat this process in the next column as there could be
up to 10 items without duplicating the any of the other materials for
that Retail Product

I know this is probably simple but am stuck