Lookup Specific Values according to Unique reference, replace if necessary

[NickPDC]

Hey again guys,

I have yet another formula that I am struggling to work out, so here is a challenge for you!

I do not know if this would even be possible, but it is worth a shot!

Attached are the workbooks I have been working from.

Basically, the "Ops Cards" workbook is what I am working from, and this is where I would like the data from another workbook to appear when the material grade, at the top is changed.
(Sample data in at the moment to show what it would look like).

The "Export" workbook is the data store, where the data is stored, ready to be displayed in the "Ops Card" workbook when the corresponding "PDC Grade" is entered.

This is where it gets tricky.

The other sheet, "Item Ingredients", contains data that may need to be entered depending if it matches the part number on the Ops Card "C37".

I want the Data Corresponding to the "Grade", in this case JU12A to be displayed in the "Ops Cards", but if there is data within the "Item Ingredients" which matches the part number "C37", that must replace the existing value from the "PDC Grade" sheet, on the "Ops Cards" workbook.

So, say Titanium content according to JU12A should be 4.5 - 6.7, but the part number requires it to be 5.6 - 7.8, the "4.5 - 6.7" will be replaced with "5.6-7.8" as the part number requested.

This is because despite a job being a certain grade of material, it may have some item specific specs which it needs to have in order to meet the customer spec!

Hope that makes sense.

Thanks!

Nick

[NickPDC]

Anybodyyyy?

[NickPDC]

New Year Bump!
Still needing assistance

[JBeaucaire]

This is how I would do it. I've added a "key" column to both of the data sheets in the Export workbook. With this, every row has a unique qualifying ID and makes a standard INDEX/MATCH a cinch.

Just change the C4 part number and the option C37 part number and watch the magic.

[NickPDC]

This is how I would do it. I've added a "key" column to both of the data sheets in the Export workbook. With this, every row has a unique qualifying ID and makes a standard INDEX/MATCH a cinch.

Just change the C4 part number and the option C37 part number and watch the magic.

That looks absolutely fantastic!
Thank you so much for the help!

There is one slight flaw though.
The materials are different for different jobs, so is there anyway to lookup the material as well, but display an empty cell when there are no other materials to input?

But basically there can be 5 materials for one part number, then 10 for another, so the cells will need to display those materials according to the number.

Hope that makes sense!

Thanks again!

Nick

[NickPDC]

Infact, that will do me fine, I just need to find a formula that will display only the Materials associated with that grade number!
If you could show me one of them, I would appreciate it!

Thanks!

[JBeaucaire]

Can't say I know what you mean.

[NickPDC]

Can't say I know what you mean.

Sorry for not making it clear! I am terrible with descriptions

The list of materials listed on the left hand side of the worksheet changes with each "Grade" eg, KU32A, JU12A etc etc.

So basically I am needing a formula to only display the materials within that "Grade"

Hope this is clearer!

Thanks, Nick

EDIT: The list of materials is not static data. This needs to be able to change as well, as there is different materials for each different grade number. That is not a complete set of data in the "Export" sheet, that is only a very small workbook for testing with. Hope this makes more sense!

[JBeaucaire]

Rather than add an whole new layer of complicated plumbing to your sheet, let's use a simple Conditional Formatting trick.

The ingredients listed in A18:A28 will remain as is, listed manually in the order you have laid out. All the other formula in the other columns are already designed to search for and only display values for ingredients found in the other workbook.

So, I've added a Conditional Formatting in A18:A28 that will hide the text (color it white to match the background) for the ingredients that have no values brought over and displayed in column D.

[NickPDC]

Rather than add an whole new layer of complicated plumbing to your sheet, let's use a simple Conditional Formatting trick.

The ingredients listed in A18:A28 will remain as is, listed manually in the order you have laid out. All the other formula in the other columns are already designed to search for and only display values for ingredients found in the other workbook.

So, I've added a Conditional Formatting in A18:A28 that will hide the text (color it white to match the background) for the ingredients that have no values brought over and displayed in column D.

Very nicely done!
Works perfectly, thank you very much for the assistance.

Is there any way of making the cells with a value of 0.00 have the text disappear as well?

Thanks!

[NickPDC]

Very nicely done!
Works perfectly, thank you very much for the assistance.

Is there any way of making the cells with a value of 0.00 have the text disappear as well?

Thanks!

Scrub that I have worked it out!

Thanks for the help!