Change cell value based on 2 values of other fields

Stefanie S.

I have a form that asks information of faculty about the rooms they use in our building. I have data from last year's process in a separate sheet.

Presently, using automation, I'm copying rows from the intake sheet/form into a new sheet that already has last year's data in it.

There is a question in the intake form/sheet that asks if their data is the same from last year. If yes, we'll use their data from last and they don't have to input anything further.

This is the sheet with the combined info and it will be continually updated based on faculty form submissions.

Either I need the Report Yr to change to 2021 on the original row if a row comes in with the same identifier and has Previous Yr Still Apply as Yes or the rows merged, or the % cells copied to the new row, anything. I've tried so many different formulas unsuccessfully and being a newb at this is hindering me.

Thank you.

Genevieve P.

Hi @Stefanie S.

I would adjust your Copy Row automation to only happen if the "Previous Yr Still Apply?" column is blank. This would keep your duplicate rows, the ones that say "Yes", in your intake sheet (so you don't have duplicates in your original sheet).

Then I would set up a Second Column in your original sheet to identify the Report Year. Keep your current one with the same name (Report Yr.) so that newly copied rows have a place for the new year to come in, but hide it in your sheet.

In your new Report Year column, we can use a column formula to see if there's a match in the other sheet that says "Yes", using your Identifier, and if there is return 2021. If there isn't, return the value in your original Report Yr. column.

This will keep the newly added rows as 2021, the old rows without a new course as 2020, but update the rows with a "Yes" in the new sheet as 2021 in this original sheet. Would this work for you?

Formula:

Here's the formula you could use in this new Report Year column:

=IF(IFERROR(INDEX({Previous Year Still Apply Column Intake Sheet}, MATCH(Identifier@row, {Identifier Intake Sheet}, 0)), "") = "Yes", 2021, [Report Yr.]@row)

{These} are cross sheet references looking at your intake sheet.

The INDEX function will bring back the value in the Previous Year Still Apply Column, matching the identifier across sheets.

The IFERROR says that if there's NOMATCH, then this indicates that there isn't a new submission for this course, so return blank (which will return the Report Yr. in the current row, instead of looking into the other sheet).

Then we have an IF statement around it saying that IF there's a YES, then return 2021. Otherwise, return [Report Yr.]@row

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Stefanie S.

Genevieve,

Thank you so much for your detailed reply. This is so helpful for me in learning the ins and outs of Smartsheet formulas. I'm still having some difficulties so please allow me to be more specific...

Intake Sheet:

Intake Sheet Automation:

Combined Info Sheet: (Displays all rows from 2020 and copied rows from 2021 Intake Form per the automation above.

I created a new Report Year column per your post. I added the formula:

=IF(IFERROR(INDEX({Previous Year Still Apply Column Intake}, MATCH(Identifier@row, {Identifier Intake}, 0)), "") = "Yes", 2021, Report Yr@row)

References:

But as you can see in Combined Info, the formula results in #UNPARSEABLE. Can you help me figure out what I'm doing wrong?

Thanks again,

Stefanie

Genevieve P.

Hi @Stefanie S.

Well-done! This is very close!

The only thing you're missing is the [brackets] around the column name at the very end:

=IF(IFERROR(INDEX({Previous Year Still Apply Column Intake}, MATCH(Identifier@row, {Identifier Intake}, 0)), "") = "Yes", 2021, [Report Yr]@row)

See: Create a Cell or Column Reference in a Formula

Cheers,

Genevieve

Stefanie S.

Hi @Genevieve P.

All I can think of is posting this to describe you. I'm so grateful!

One last thing, is there a way to enforce the combined info sheet to save after running the formula? I am feeding these numbers to a report on a dashboard that faculty will reload upon submission and noticed when the formula runs, it doesn't display the row unless the sheet is manually saved or another submission is made on the intake form.

Genevieve P.

Hi @Stefanie S.

I'm so glad I could be of help!

In regards to refreshing, the formula should refresh within a few seconds without you needing to save this sheet. I will note that we were seeing delays with cross-sheet formulas and cell links yesterday, but this is now resolved! Are you still seeing a delay today?

Cheers!

Genevieve

Stefanie S.

@Genevieve P.

Everything is resolved - thank you again!

Genevieve P.

Wonderful, I'm glad to hear it! 🙂