Can I create a new row in a separate sheet automatically without using Automation?

Nancy Kuhns

Hello,

I'm working with multiple sheets that have some of the same information across each of them. I have employed DataMesh, but there are a few key pieces that I feel like I still need, but may just be missing the know-how to execute:

1) I am working with Resource Names across several sheets. I want the functionality that when I add a new row in Sheet A with a new resource name, it automatically creates a new row in Sheet B with just that resource name (I know with automation you can move or copy the row.. I do not want all the columns from Sheet A in Sheet B). Is there existing functionality or a formula that I am missing that would do this for me?

2) I am summing hours across sheets, currently using the following formula: =SUMIF({Name}, "Jane Doe", {Wk Sched Hrs Per Project}). This is currently manual, where I have to type in the Resource Name that is on the current sheet, while pulling the Name and Wk Sched Hrs Per Project from another sheet. Is there a way to automate this so that if a new resource row is added (as in question 1 above), that the hours are automatically summed here based on the formula pulling in the information automatically?

Thank you for any help you may provide!

Genevieve P.

Hi @Nancy Kuhns

1) For your first scenario, this is a great use for DataMesh! (See: Smartsheet DataMesh: Avoid Duplicative Data Entry)

You're right, a Copy Row automation would copy the entire row, not just one name. DataMesh can look into your destination sheet and see if there are any duplicates (and skip them), only creating new rows for new Resource Names.

You would set it up so your Resource Name column is the Lookup Value across the sheets, and the only column to populate as Data Field 1. Then make sure for Duplicates in the Source Sheet you "Pick 1st Match" so it doesn't create duplicates in the destination.

This will then add in new unique values from the Resource Name column but skip over any previously entered names.

2) If I'm understanding you correctly, you already have the Resource Name in a column next to this formula, is that correct? If so, then all you need to do is swap out the "name reference" in quotes to instead reference the cell in this row, like so:

=SUMIF({Name}, [Resource Name]@row, {Wk Sched Hrs Per Project})

Then you can apply this as a Column Formula so that it will automatically populate and update as new resources are added in your Resource Name column! See: Create a Cell or Column Reference in a Formula and Create Efficient Formulas with @row.

Let me know if you have any questions about this or would like to see screen captures of how I set up my test config or sheet.

Cheers!

Genevieve