automatically put in values from a price list

[Guest]

Hi All,

I have a price list with 60+ accessories whose prices always change. There
is another list of about 30 parts that use different combinations of these
accessories to make a final part. I need to update these values monthly, and
instead of updating the same one over and over, I would rather update the
accessories list and have the list of the 30 parts automatically update from
the accessories list.

Any ideas?

Thanks in advance,

Cam

 

[Zone]

Cam, are the two lists on the same sheet or different sheets? You could
write a formula to add up the prices of the individual accessories that make
up each part. If there are a lot of accessories in each part, you could end
up with a very long formula. Might be easier to do it with a macro. James

 

[Guest]

Hey Zone,

They are on different sheets. I was hoping there is some formula that says
"when x is in this cell, return the value to the left of x on this sheet".

Thanks!

 

[Zone]

Hi Cam,
Sorry I wasn't able to get back to you earlier. You'd have to explain the
layout of the two sheets and give me a specific example of what you're
wanting to do. Do some accessories go into more than one part? James

 

[Guest]

Hi Zone,

Thanks for the continued support on this!

I have two sheets, each is a list of about 30 parts. The first sheet is a
list of 30+ different parts (the accessories) such as nuts, bolts, washers,
and other things we use to assemble final parts with the price of each beside
it. The final parts are what are on the second list. All of the parts on
the second list are pretty similar, but there are slight difference in their
size, what gets bolted on them, the size of bolts and washers etc. Because
of this all parts on the second list (the final parts) use a slightly
different mix of what is on the first list (nuts, bolts etc).

What I have is a bill of materials for all these final parts that lists what
combination of accessories each one uses. When the price of an accessory
changes it becomes a pain to search through each part and change the price
manually. So what I was hoping to do is get the second lists, the final
parts list, to automatically update from the first list.

I hope this helps.

Thanks!

 

[Zone]

Hi Cam,
I'll assume both sheets have the part names (or numbers) in column A and the
price for each in column B. How we approach this really depends on how many
accessories might be used for each part. Can you estimate the most
accessories that might be used for one part, and whether multiples of each
accessory might be used for one part, such as 5 of a particular screw? You
might end up with, say 15 DIFFERENT accessories for one part, but with
multiples of individual accessories making a TOTAL of 30 accessories for a
particular part. Let me know and we'll proceed. James