Form from one sheet populates another sheet

Paul Grim

Hello SS gurus,

I am relatively new to SS "in the field", although I've watched over 20 hours of videos from the SS Learning Center so I have a good understanding of the mechanics. Here is what I'm trying to do...

We need to track inventory in our supply room: items on hand, items removed, items added, etc. We would prefer that the end user indicate what they are removing from the supply room using a web form, rather than directly updating a sheet. Also, we would like them to indicate items that have been added to the supply room by using a separate web form. This will enable us to very clearly see a record for each time an item is added to or removed from the supply room.

I would like a "master sheet" that lists all the items we stock in our supply room, along with details such as vendor name, item number, minimum quantity needed on hand, etc. This is what would drive the two web forms (one for "removing" items and one for "adding" items).

Then I would like a "detail sheet" where a row is added each time an item is removed or added to the supply room. Remember there would be two web forms: one for removing items (where the quantity would be a negative number), and another for adding items (where the quantity would be a positive number). Each form would pull the item details from the "master sheet", but upon submitting the form they would send the new row to the "detail sheet" so that we had a record of every time something was removed or added to the supply room.

Finally, I would like a rolled-up "summary sheet" that summarizes the "detail sheet" by aggregating the additions and subtractions of each item to and from the supply room so that we could see what is the current quantity on hand of every item. I would like this to be a sheet and not a report because I want to have alerts triggered when the quantity on hand of each item drops below a certain level as indicated on the "master sheet".

Example - The "Master Sheet" indicates we carry large gloves and always need 5 pairs on hand:

Now imagine the "Addition" web form is submitted indicating that a shipment of 10 pairs has been received into the supply room, thus adding a new row to the "Detail Sheet" as follows:

Then another employee submits the "Removal" web form indicating they removed 3 pairs of large gloves, and a new row is added to the "Detail Sheet" as follows:

The "Summary Sheet" should roll these up into a Qty. on Hand record of "7" and display as follows:

If another employee then submits the "Removal" web form to indicate 5 more pairs of gloves have been taken, another record is added to the "Detail Sheet" as follows:

And now the "Summary Sheet" should reflect the new Qty. on Hand of "2" as follows:

Since the "Qty on Hand" is less than 5 (which is the "Min. Qty" indicated for this item in the "Master Sheet"), an alert should be triggered to notify someone to order more of this item.

Is this possible? Am I even going about this in the right way? If there's a better way to accomplish this I'm all ears! Thanks!

Paul Newcome

You would use a SUMIFS with cross sheet references like so...

=SUMIFS({Detail Sheet Qty Column}, {Other Sheet Item # Column}, [Item #]@row)

Genevieve P.

^^ I agree with what @Paul Newcome suggested!

However I think you could simplify this by having your "Master" sheet and your "Summary" sheet be the same thing: the Master sheet with a few extra Summary columns. That way you can have the Min Qty column next to the summary Qty on Hand column (which is using Paul's formula above), and can add a third column to raise a red flag symbol (as an example) to indicate if the Qty on Hand is lower than the Min.

This would be a pretty simple IF statement that we can help you build, if needed. Then you could either create Reports showing any rows that have this flag raised, or set up alerts/notifications based on this information. Does that make sense?

Paul Newcome

Forms will only populate the sheet they are created on.

If you are just doing basic tracking for alerts and whatnot, it would definitely be easier to manage all on one sheet.

The benefit of having a second sheet for the metrics would be an easier to read version of the counts.

It is primarily up to personal preference and overall workflow as to what works best for you. I personally use a lot of "rollup" or "summary" sheets because I have a lot of data that needs displayed on dashboards and whatnot, but some of the more straightforward tracking sheets that are only setup to automate alerts and whatnot I do try to keep on one sheet when I can.

Paul Newcome

You are correct on each of your points.

Smartsheet does not currently offer conditional logic on their forms. There are a couple of options for this...

You could use a 3rd party app that does offer it such as Google Forms which can be integrated into Smartsheet.

You could build out a table that lists each of the applicable units of measure for each of the items. We could make this work with a two column table. Then we would use a helper column (which can be hidden if you want) on the sheet collecting the forms that essentially does a lookup on the table based on the item selected and compares it to the selection made. We could then set up an Automated Update request to be sent to the person who submitted the form letting them know that the unit of measure they selected is incorrect and they need to select from one of the following (insert placeholder for helper column here to dynamically list applicable measurements).

Paul Newcome

You would use a SUMIFS with cross sheet references like so...

=SUMIFS({Detail Sheet Qty Column}, {Other Sheet Item # Column}, [Item #]@row)

Genevieve P.

^^ I agree with what @Paul Newcome suggested!

However I think you could simplify this by having your "Master" sheet and your "Summary" sheet be the same thing: the Master sheet with a few extra Summary columns. That way you can have the Min Qty column next to the summary Qty on Hand column (which is using Paul's formula above), and can add a third column to raise a red flag symbol (as an example) to indicate if the Qty on Hand is lower than the Min.

This would be a pretty simple IF statement that we can help you build, if needed. Then you could either create Reports showing any rows that have this flag raised, or set up alerts/notifications based on this information. Does that make sense?

Paul Newcome

I forgot about the Min Qty. Yes. It should be a pretty straightforward IF statement whether you are using one sheet or two.

Paul Grim

Thank you very much for your suggestions. I hadn't thought about Genevieve's idea to store the quantities on the Master sheet, thus negating the need for a separate Summary sheet. I think I understand how to carry that out, so thanks!

I also think I understand Paul's cross-sheet reference formula and how to implement that, so thanks!

As for the web form, is it possible to have the form be based on the Master sheet, but populate data on a separate Detail sheet? I didn't know if that was possible or not, as the only web form I've created simply added rows to the sheet from which it originated, whereas I want it to populate a different sheet.

Thank you both again for your assistance while I get up to speed!

Paul Newcome

Forms will only populate the sheet they are created on.

If you are just doing basic tracking for alerts and whatnot, it would definitely be easier to manage all on one sheet.

The benefit of having a second sheet for the metrics would be an easier to read version of the counts.

It is primarily up to personal preference and overall workflow as to what works best for you. I personally use a lot of "rollup" or "summary" sheets because I have a lot of data that needs displayed on dashboards and whatnot, but some of the more straightforward tracking sheets that are only setup to automate alerts and whatnot I do try to keep on one sheet when I can.

Paul Grim

I guess the wrinkle for us in keeping everything on one sheet is twofold:

1) I would need to first import all the potential supply room items with a quantity of "0". Then, new rows will be added to the sheet as the form is submitted every time someone adds/removes something from the supply room. I just thought that might be confusing, having one record for each item that has no quantity in it, since it's only a "placeholder" record for the form's benefit (so that the item is available to be selected in the form).

2) What if a reference value for an item were to change, such as the minimum quantity? I would now have some records with the "old" minimum quantity and some records with the "new" minimum quantity. But if I kept a Master sheet of items, then I could easily manage their default values and such.

Which reminds me: Smartsheet doesn't offer context-sensitive filtering in the forms, do they? By that I mean, when you select an item from one field, the other fields don't filter themselves to only offer the selections that pertain to that item. So if you select the item "bubble wrap", the form would display every unit of measure for ALL items, not just the units of measure for bubble wrap which might be "sheet" and "roll". I worry that will also cause problems with inventory, when someone selects a unit of measure that is not valid for that item (such as if they order a "quart" of bubble wrap, etc.)

Paul Newcome

You are correct on each of your points.

Smartsheet does not currently offer conditional logic on their forms. There are a couple of options for this...

You could use a 3rd party app that does offer it such as Google Forms which can be integrated into Smartsheet.

You could build out a table that lists each of the applicable units of measure for each of the items. We could make this work with a two column table. Then we would use a helper column (which can be hidden if you want) on the sheet collecting the forms that essentially does a lookup on the table based on the item selected and compares it to the selection made. We could then set up an Automated Update request to be sent to the person who submitted the form letting them know that the unit of measure they selected is incorrect and they need to select from one of the following (insert placeholder for helper column here to dynamically list applicable measurements).

Paul Grim

Good suggestions Paul, thanks. I will have to do some experimenting to see what works best for us!

Paul Newcome

Happy to help! 👍️

Feel free to revisit if you would like further assistance getting things set up.

Paul Grim

I just tested an idea that I think will work for us. Rather than storing everything in a Master sheet, I can still have a separate Detail sheet by using the Move Row feature to take any rows submitted through the form (which originates from the Master sheet) and automatically moving them from the Master sheet to the Detail sheet. Worked like a charm when I tested it!

Paul Newcome

@Paul Grim Let me start with... Working is what matters.

But... It does seem overly complicated. Why not just have the form populate the detail sheet directly instead of populating the master sheet and then moving to the detail sheet? From the sound of it, your Master Sheet will essentially remain blank since the new rows are moved.

Additional note you might be interested in... I saw another post here in the Community early this morning, and apparently Conditional Logic in forms is on the way soon! This means that (after it is rolled out) you should be able to limit what options can be selected in the form fields based on previous selections (such as units of measure based on item selection).

Paul Grim

Because my Master sheet will contain a single record for each item of inventory, along with all the pertinent details that I might not want cluttering up my Detail sheet (vendor name, item #, min. qty., max. qty., etc.) Think of my Master sheet as a list of students at a school, with one record for each student (because each student only exists once).

My Detail sheet is a list of all items that are added to or removed from the supply room over time, with details such as who added/removed the item and when. Think of my Detail sheet as a list of all the classes each student takes, with one record for each class for each student, but also multiple occurrences of each student (because students take multiple classes).

My Master sheet is where the form must originate from, since it is the only place where every possible item is listed. Since my form is based on the Master sheet, any records submitted through it will populate the Master sheet, which is not where I want them because it will make it difficult to find and maintain the "master" record for each item since the day-to-day additions and removals from the supply room will be mixed in there as well. But I can set up a rule that immediately moves any new rows added to the Master sheet over to the Detail sheet.

I hope that makes sense? I come from a database background, so keeping master item records separate from detail inventory records is a natural way for me to organize my data.

And yes, I just saw the announcement about conditional logic in forms, so that will be awesome! 😀

Paul Newcome

I completely understand the separation of data between "Details" and "Master" and the reason for it. It is just my personal way of doing things to try to cut out as many steps as I can while still maintaining a working solution.

Again... What matters is that it is working.

At this point I am just kind of thinking out loud. As long as you don't mind my questions, I like to learn different approaches as much as I can because the same approach does not work as well for every single scenario. I'm not trying to question your methods as "wrong". I am just trying to understand the "why" when it comes to the Move Row step.

Why not use a dropdown for your item selection on the Detail Sheet where you can pre-fill the dropdown with all of the possible selections (and for your other selection columns as well)? I would think that you would be able to then generate your form from the Details sheet which would cut out the Move Row "Middle-man" and still accomplish the task with one less thing that could potentially break.

Paul Grim

I don't mind questions at all! I like being forced to think (and rethink) my approach. All I care about is making things easy for me to manage and for end users to interact with, so I'm happy to change approaches if it benefits all involved.

Drop-downs on the Detail sheet are a possibility, except that we have hundreds of items in inventory, so that would sure be one long drop-down list! 😀 Plus then I wouldn't be able to associate items in one drop-down (such as the item description) with another drop-down (such as the item size).

But to your point, I may need to build the drop-downs anyway so that users can select them in the form. But I think I'm going to wait to see the new forms enhancements first before I start building anything, since that could change my whole approach!

Paul Newcome

Haha. That would be quite a long dropdown list. There is a thread out here in the Community somewhere where we actually tested the limits of single and multi-select dropdowns. Single select was somewhere around 5,000 (I think) and multi-select I gave up after 50,000 (or somewhere around there). The biggest limitation I ran into with multi-select was the 4,000 characters per cell limitation. So you should still be well within the column limitations themselves even with such a large list.

You can also use copy/paste to input large lists into a dropdown so long as they are already organized vertically. So if your master sheet has all items listed down a single column, you can actually select all of those then copy/paste into the dropdown column properties.

Additionally... Since you are creating a master separate from the detail, The detail sheet could actually house extra columns that normally would "clutter" the sheet but aren't going to be used or viewed directly so won't have much impact as far as that goes.

Couple this limited visibility with conditional logic on forms, and you could create separate dropdowns for the different types of measurements (weight/volume/count/etc) then possibly use the logic in the form to say that if Item A, B, or C, is selected, show the Volume Measure Dropdown, but if Items D, E, or F are selected show the Weight Measure Dropdown and the non-applicable fields would remain hidden as the user fills out the form.

I have a pretty good feeling that the form updates are going to open up a whole new realm of possibilities within Smartsheet itself. 😉

If I understand your concern about associating column to column such as Item compared to Size correctly, it could actually end up being pretty straightforward. There are numerous functions that allow you to look at multiple columns. For instance...

=COUNTIFS({Detail Sheet Item Column}, "Item A", {Detail Sheet Size Column}, "Large")

will count how many rows contain both Item A in the Item column AND Large in the size column.

=SUMIFS({Detail Sheet Cost Column}, {Detail Sheet Item Column}, "Item A", {Detail Sheet Size Column}, "Large")

will sum up all of the rows in the cost column that contain both Item A in the Item column AND Large in the size column.

And if you are using these formulas on your Master sheet and already have everything listed out, then you could use cell references instead of specific text so that you only have to write the formula one time and can then dragfill to automatically account for what is in your list.

Using the above 2 points would take a single dropdown of

Item A Large

Item A Medium

Item A Small

Item B 10oz

Item B 5oz

Item B 1oz

Item C Large

Item C Medium

Item C Small

To a more flexible 3 separate dropdowns (with conditional logic in the form only allowing applicable selections) of

Item...............Size Measure...............Weight Measure

Item A...........Large.............................10oz

Item B...........Medium.........................5oz

Item C...........Small.............................1oz

If they select Item A or C then the Size Measure field appears and the Weight Measure field remains hidden. If they select Item B then the Size stays hidden and the Weight appears.

I hope that rambling makes sense.