Can I make this use case work in Smartsheet?

A&W Admin

Hi,

I've been trying to solution a use case, and I'm curious if we can do it in Smartsheet. The use case is ordering materials for individual restaurants based on specific criteria. I was thinking that I can set up one sheet with the restaurant specific information (name, address, city, other required criteria), and a second sheet with the materials that are required for the order. But now I need to create a report (?) where you merge the restaurant information with the material list to create a list of materials for each restaurant based on the information in both sheets. So, for example, the materials list might say restaurants in Alberta need 4 chairs and restaurants in BC need 3 chairs, so the final output would have 4 chairs assigned to all restaurants in AB and 3 to restaurants in BC. I would need both a list of the individual restaurants with the orders assigned to them, as well as a rollup of how many chairs need to be ordered to send to the manufacture. I should add that this would be a stand alone workspace, and not tied to any other data that we have in our Smartsheet solution.

I've tried reporting, and it doesn't seem like I can easily get the output I want. I'm not sure if control centre would work? Are there any other Smartsheet apps that might assist? Or does this sound like a use case that's better solutioned outside of Smartsheet? Thank you!

Andrée Starå

https://community.smartsheet.com/discussion/122062/can-i-make-this-use-case-work-in-smartsheet

Hi,

I hope you're well and safe!

Yes, it seems like it should work just fine.

Do you have access to any of the premium apps?

Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing.) That would make it easier to 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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jgorsich

Control center is primarily a fancy way of redeploying sets of sheets over and over again and accumulating the results - doesn't really seem like anything that would help you.

What you are describing sounds like a fairly standard (which doesn't mean simple) database and summary situation.

First - you need a list of inputs (possibly multiple lists). For your scenario, this would maybe include something like a sheet with 4 columns: "RequirementArea", "CriteriaType","Criteria","Quantity" filled out with, in your example these two rows of data:

Chairs/Region/Alberta/4

Chairs/Region/Alberta/3

You can well imagine you could add lots of different possible things to this and it should be able to cover most everything you need.

Second - you need a sheet that lists your different restaurants and your different Requirements. It might have columns that look like this: "NumberOfRestaurants","Region","Type","Chairs","Tables","Pans"

Your columns "Number of Restaurants", "Region", and "Type" (or others you add) would get filled in with data manually while your columns "Chairs", "Tables", "Pans" would be populated with formulas. So… you might have 2 rows that look like this:

2/Alberta/Cafe (meaning you are needing order quantities for 2 cafes in Alberta)

3/Alberta/Large (meaning you are needing order quantities for 3 large restaurants in Alberta)

while your formula column in Chairs might look like "=NumberOfRestaraunts@row*(sumifs({Sheet1_Quantity},{Sheet1_RequirementArea},"Chairs",{Sheet1_Criteria},"Alberta"))"

Obviously - you could make that a LOT more complicated - at the least, you'd probably want your table count first, then multiply your chairs by that THEN multiply by the number of restaurants, etc.

THIRD - finally you'd want an actual order sheet that, for each thing you need to buy, would simply sum the columns from Sheet2.