How to report on information from 2 sheets with different primary columns?

Jen Henning

We are using Smartsheet as our PLM solution. I have sheets that contain all information associated to items (primary column = item#) and I have a master material database that contains all information associated to the materials used in each product (primary column = material item #) I am trying to figure out how to create the following reports:

1) Report listing all items that pulls in all material detail from the material database based on the material item# assigned to each product

2) Report listing all materials that pulls in all item details from the item sheet in which that material is assigned to

I realize I can accomplish this by linking in ALL fabric data to my Product sheet using vlookup to the fabric item#, HOWEVER, I really don't want to have to store all 40-50 fabric fields in my product sheet. My product sheet is already well over 300 columns - I only want to keep the ONE fabric identifier field (fabric item#), but then be able to report on items along with all the fabric details from the fabric database.

Is there any way to do this without having to store all columns in the product sheet and do vlookups to them all?

Thank you in advance!

EXAMPLE:

SHEET Setup:

REPORTS I am looking for:

Bassam Khalil

Hi @Jen Henning

Hope you are fine, please share me as an admin on a sample workspace contains your sheets and i will create the exact formula (after removing or replacing any sensitive information).

My Email : Bassam.k@mobilproject.it

Andrée Starå

Hi @Jen Henning

I hope you're well and safe!

Depending on the structure and specifics, you could use cell linking instead.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!