How Can I update certain cells in a another sheet when a row is added to original submission sheet?

Mohamed.muse

Hi, I'm new to Smartsheet

I have two sheets.

The first sheet holds information submitted via a form

The second sheet holds similar information but its data is used for a dashboard (

1. Usually, an employee will submit a form and a new row will be added to the submission sheet (first sheet).
2. I will take certain data points from the new row in the submission sheet and manually update certain cells in the (Second Sheet).

I want to automate this manual update process. How can have the cells in the second sheet updated once a new row is add the first sheet.

Debbie Sawyer

@Mohamed.muse

Hi Mohamed - I hope you don't mind, but for the benefit of this community post thread, I thought I'd just summarise what we did on our zoom call so that people searching in the future can see the answer.

You then explained that Sheet 1 was an update sheet where people enter the lastest information about each grant. The Grant Name is Unique to the Grant and can be used as a reference point, but as sheet 1 is an update sheet, each grant is mentioned multiple times. You wanted sheet 2 to automatically pick up the latest entry per Grant.

We set up 2 helper columns in sheet1, The first called ONE with =1 as the column formula. The second called RowNum with =SUM(One$1:One@row) as the function. This is dragged to all used rows in the sheet to create a RowNumber which can be referenced in a formula.

We then went to Sheet 2 and used an Index formula to pick up the latest Grant Value from Sheet 1.

=INDEX({sheet1's Grant Value column},MAX(COLLECT({Sheet 1's RowNum, {Sheet 1's Grant Name},Grant Name on Sheet 2 current Row)

This did what you wanted.

However, this will only work IF Sheet 1 is sorted in Create Date order with the latest entry being added to the bottom of the sheet.

This worked for you.

To pull alternative values from the latest entry for each Grant on Sheet 1, we only need to update the Index part of the formula (in bold below) to reference the column that you are pulling back to sheet 2.

=INDEX({sheet1's Grant Value column},MAX(COLLECT({Sheet 1's RowNum, {Sheet 1's Grant Name},Grant Name on Sheet 2 current Row)

I do hope you went on to update some other columns in your sheet.

You know where I am if you need further help.

Kind regards

Debbie

Lucas Rayala

Hi @Mohamed.muse, you might need to add some screenshots so we can see how your data is set up on both pages -- that's important to give you an answer. The swiss-army-knife of reference formulas is:

=INDEX(COLLECT({first sheet data column}, {shared value between pages}, sharedvalue@row),1)

This requires you to have a shared value to reference on both pages. You can google Smartsheet Collect function to understand how it is used to narrow down the information you need. If this doesn't help, again, add some screenshots so we can get a better idea. Good luck!

Mohamed.muse

@Lucas Rayala Thank you for suggestions. Here are some screenshots

Sheet one:

Sheet two: Where the information needs to be updated

I want to note again that the first sheet is a form submission sheet and a row is added to the bottom of the sheet every time an employee makes a submission

Debbie Sawyer

Hi,

The Index Collect statement that Lucas mentioned will return a Total Award Amount value in sheet 2 where it finds a match in sheet 1. You could also use an Index(Match()) function to do the same sort of thing. In both these functions the Total Award Amount could be pulled into sheet 2 from sheet 1 IF the Grant Name is UNIQUE in sheet 1.

If the Grant Names are unique in sheet 1, then you could say:

=INDEX({Sheet 1 - Total Award Amount}, MATCH(Sheet 2's Grant Name on the current row, Sheet 1's Grant Name,0))

However, if the Grant Name on Sheet 1 isn't unique then this won't work for any rows below the first one where Gear was used.

If Grant Name can be used more than once in Sheet 1, are you looking for a Total of that grant in sheet 2? If so, you could use SUMIF(Sheet 1's Grant Name column, Sheet 2's Grant Name on the current row, Sheet 1's Total Award Amount).

I hope this helps a little - it is all about the relationship you are looking for between the sheets.

Kind regards

​Debbie Sawyer - Chief Smartsheet Solutions Officer (CSSO)

Mohamed.muse

@Debbie Sawyer Thank you. The Grant name in sheet one is not unique and the reason is every time a submission is made a new row is added to sheet 1 with a duplicate grant name. To answer your question, No, I'm not doing grant total. I'm trying to take the new info anytime a row is added to sheet 1 and match it to the appropriate cells in sheet two. I wanted to use the columns grant name as a reference but I just realized that it is not unique.

Debbie Sawyer

@Mohamed.muse

Thanks for your feedback.

Have you explored the "Copy Row" workflow at all?

You could build an automation that triggers when a row is added and copies that row to your Sheet 2. It will copy the entire row, but in Sheet 2 you could just hide the columns you are not interested in.

Just a different way of achieving your aim without the need for complex formula or unique ID's!

Mohamed.muse

@Debbie Sawyer, I explored it then I realized that I use the second sheet to update existing records rather than add new rows. Will adding a unique ID to each form submission help?

Debbie Sawyer

@Mohamed.muse

I don't understand why updating the rows in sheet 2 would stop you creating them by copying them from sheet 1. I am more than happy to have a quick chat on Zoom if you'd like to discuss your options here?

My email is debbie.sawyer@smarterbusinessprocesses.com, we could zoom, you could explain what you are after and I could see if there is a quick win for you. (No cost!)

I am around for approximately another 40 mins today. (until 5pm GMT)

Kind regards

Debbie

Debbie Sawyer

@Mohamed.muse

Hi Mohamed - I hope you don't mind, but for the benefit of this community post thread, I thought I'd just summarise what we did on our zoom call so that people searching in the future can see the answer.

You then explained that Sheet 1 was an update sheet where people enter the lastest information about each grant. The Grant Name is Unique to the Grant and can be used as a reference point, but as sheet 1 is an update sheet, each grant is mentioned multiple times. You wanted sheet 2 to automatically pick up the latest entry per Grant.

We set up 2 helper columns in sheet1, The first called ONE with =1 as the column formula. The second called RowNum with =SUM(One$1:One@row) as the function. This is dragged to all used rows in the sheet to create a RowNumber which can be referenced in a formula.

We then went to Sheet 2 and used an Index formula to pick up the latest Grant Value from Sheet 1.

=INDEX({sheet1's Grant Value column},MAX(COLLECT({Sheet 1's RowNum, {Sheet 1's Grant Name},Grant Name on Sheet 2 current Row)

This did what you wanted.

However, this will only work IF Sheet 1 is sorted in Create Date order with the latest entry being added to the bottom of the sheet.

This worked for you.

To pull alternative values from the latest entry for each Grant on Sheet 1, we only need to update the Index part of the formula (in bold below) to reference the column that you are pulling back to sheet 2.

=INDEX({sheet1's Grant Value column},MAX(COLLECT({Sheet 1's RowNum, {Sheet 1's Grant Name},Grant Name on Sheet 2 current Row)

I do hope you went on to update some other columns in your sheet.

You know where I am if you need further help.

Kind regards

Debbie

Mohamed.muse

Thank you so much @Debbie Sawyer 😄