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