Populate a cell from "sheet 1" to a matching row on "sheet 2" if a checkbox on "sheet 1" is checked.

Malachilee

Hello,

I am building an inventory system. I have a form for new items purchased. When filled out, it populates "sheet 1". On that form is a checkbox for "New Item."

When checked, it triggers automation to send the "New Item" data to our main Inventory Sheet "sheet 2" It populates all data except the quantity.

I would like the quantity to be populated when the shipping and receiving person checks the item was delivered on "sheet 1" at a later date.

I have been trying everything I can find online, but nothing is working for me. I am relatively new to SmartSheets, so I have been learning from these forums and trial and error.

This is my latest attempt.

=INDEX({Purchased Inventory Range 1}, 1, MATCH([Stock Room]@row, {Purchased Inventory Range 2}, 0))

Jason Tarpinian

=INDEX({Sheet 1 Stock Room Range},MATCH([M Code]@row,{Sheet 1 M Code Range},0))

You should be able to build this formula into your Sheet 2. I'm not sure of your range names, but it looks like you're on the right track with your INDEX/MATCH formula. It'd be something like above, as long as what I've named "M Code" is a unique value on each sheet, this should work for you.

Malachilee

@Jason Tarpinian Thanks for helping get this to populate from the first sheet to the second sheet with the correct data. However, it is not checking to see if the check box is checked on sheet one. Should there be an IF statement in there as well? If the checkbox on sheet 1 is checked, then do "=INDEX({Purchased Inventory Range 2}, MATCH([Component Description]@row, {Purchased Inventory Range 1}, 0))"

Jason Tarpinian

Oh sorry, forgot that part! The checkbox portion would be something like this:

=IF(INDEX({Delivered},MATCH([M Code]@row,{Sheet 1 M Code Range},0))=1,TRUE,0)

where the TRUE statement is the first formula I had written, INDEX({Sheet 1 Stock Room Range},MATCH([M Code]@row,{Sheet 1 M Code Range},0)). So it would first check if the M Code delivered is checked, and if it was checked, it would run the lookup for the value, if not it would return 0. Just doing this in my head, but I believe then the whole formula is:

=IF(INDEX({Delivered},MATCH([M Code]@row,{Sheet 1 M Code Range},0))=1,INDEX({Sheet 1 Stock Room Range},MATCH([M Code]@row,{Sheet 1 M Code Range},0)),0)

Malachilee

@Jason Tarpinian Sorry that did do it. I just had something named incorrectly. However, the checkbox function is backward. It fills the cell on sheet 2 when the checkbox is unchecked. How do I switch it to fill the cell when the checkbox is checked?

Malachilee

This is what I am using, and it works great. But the checkbox function is backward.

This is from your first response.

=INDEX({Purchased Inventory Range 2}, MATCH([Component Description]@row, {Purchased Inventory Range 1}, 0))

Jason Tarpinian

Make sure that your initial logic statement = 1 (as shown in green below), when comparing a checkbox 1 = checked. If it's backwards in your formula, you'd just have to replace the TRUE (red) FALSE (blue) below. Or change the green to =0 instead of =1.

=IF(INDEX({Delivered Range}, MATCH([M Code]@row, {M Code Range}, 0)) = 1, INDEX({Stock Room Range}, MATCH([M Code]@row, {M Code Range}, 0)))