Relating sheets many to many

mfoster

Hi there. I am trying to design a set of sheets in Smartsheet to allow two different types of projects to be related to one another. I have species projects in one sheet and site projects in another sheet. I want to relate these two sheets such that one species can be related to multiple sites and one site can be related to multiple species. I know this is a different use case than many, but I think it's similar to someone wanting to have a table of orders related to a table of products, where an order could have one or 100+ products, and one product may exist in one or 100+ orders. One goal with this would be to click on one order and display all the products (and all product info stored in that table) or to summarize that, and also conversely, click on a product and display all the orders and allow summarizing.

Is this possible in SmartSheet?

Thanks

Paul Newcome

Are you able to provide some screenshots with manually entered data for reference that shows what you are wanting to accomplish?

mfoster

Thank you Paul. Yes - please see attached a very old example of what I'm referring to. This was done in Access but I'm looking for a way to accomplish it in SmartSheet.

https://docs.google.com/document/d/1HeO4MG9CkdoyrTj_WndQrkbwKEyoqI7ah_78HYokJno/edit?usp=sharing

L_123

You need to use index(collect(),count(abovecell$1:abovecell)+1). Have your selection columns be to the left in their own columns, then build out the columns you want to return in your lookup be to the right. Use the formula above to return your values you want, based on the criteria in the lookup fields. Make sure to lock all of the return columns so people don't try to edit them.