Count columns based on drop down select

DHewitt

I want to be able to have a sheet that lets me select items we make from a drop down list then check the components needs to make that item. Then have it multiply the materials needs by the number we want to fabricate and total the needed materials. After that I would like to have the sheet check our on hand materials from another sheet for stock and check a box if we have the materials on hand.

Appreciate any help, I am new to this and the formula I am trying to get to work seems complicated.

Frank B.

To achieve your goal in Smartsheet, you can follow these steps:

1. Create a sheet where you will list all the items you make in a column, and their corresponding components in the following columns. For example, the first column could be called "Items", and the second column could be called "Components".
2. Create a drop-down list for the "Items" column. To do this, select the entire column, click on the "Data Validation" button in the toolbar, choose "List" as the validation type, and enter the items you make as the options.
3. Enter the components needed to make each item in the corresponding cells in the "Components" column.
4. Add columns for the quantity of each component needed and the total materials needed. For example, you could add a column called "Quantity Needed" and another called "Total Materials Needed".
5. Use formulas to calculate the quantity of each component needed based on the number of items you want to fabricate. For example, if you need 2 items and each item requires 3 bolts, the "Quantity Needed" for bolts would be 6. You can use a formula like this: = [Quantity of Items] * [Quantity of Components].
6. Multiply the quantity of each component needed by the unit cost of that component to get the total materials needed. For example, if each bolt costs $0.50, the "Total Materials Needed" for bolts would be $3.00. You can use a formula like this: = [Quantity Needed] * [Unit Cost].
7. Create a connection to the sheet where you track your on-hand materials. To do this, go to the "Data" tab in the toolbar, select "Connect to Data", and choose the sheet where you track your on-hand materials.
8. Add a column to check whether you have the materials on hand or not. For example, you could add a column called "On-Hand" with a checkbox for each component.
9. Use a formula to check whether you have enough on-hand materials to fabricate the desired number of items. For example, you could use a formula like this: =IF([Total Materials Needed] <= [On-Hand Quantity], "Yes", "No").

By following these steps, you should be able to create a sheet in Smartsheet that lets you select items from a drop-down list, checks the components needed to make that item, multiplies the materials needed by the number you want to fabricate, and checks whether you have the materials on hand.