Referencing Other Rows Using Absolute References?

David Acord

Hi team,

I have a sheet where I know what I'm trying to do, but not sure how to go about doing it (or if it's indeed possible).

The sheet in question (funnel) has new entries added via automation once another sheet (requests) is filled out. For each line entry in the funnel, there will always be at least one value in the Developer column.

Recently, we've introduced parent/child rows to help keep more complex projects tracked. An example would be rows 8 - 10 or 11-13 in the screenshot below.

I also have reports that allow users to see just their assigned work as opposed to looking at the larger sheet, but I am getting requests to include in their report, the other child entries for their projects (so they know who to work with).

My question is: Is there a formula I could use as a column formula to display the Developer name of the row below any given cell if it has a keyword (say, "OLT Build") in the Project Name cell?

Again, using the screenshot as an example, I would ideally like the column "proposed new column" to show 'Angela' on row 9, because she's currently listed as the developer in row 10. Likewise, David should show on row 12, since he's the developer on row 13. However, not every row would need this - it should only apply to child rows (and my workaround for this would be if we could include an IF statement where the Project Name contains "OLT Build" for example. I've included arrows here to show which cells would need to be displayed in the new column.

2022-02-18_16-15-39.png

I apologize for the horrendously long explanation. I hope you can make some sense of this, because I can't!

Genevieve P.

Hi @David Acord

There currently isn't a way to lock a reference to "the row below" and then apply it as a column formula to automatically populate with every new row. That said, we could build something out to manually reference the row number below, and then if you drag-fill the formula down the column it will auto-update the row number to be one below for each row.

For example, I can put this in row 2:

=IF(CONTAINS("OLT", [Project Name]@row), Developer3)

You'll notice that I use @row for the Project Name because I'm looking in the current row for "OLT", then I use 3 after the Developer to reference the row below this current one.

If you want to check the row below for "OLT", you can change the @row to the row number below the one you're working with as well:

If you need both criteria to be true (the current row says OLT and the one below says OLT, too), then try this:

=IF(AND(CONTAINS("OLT", [Project Name]@row), CONTAINS("OLT", [Project Name]3)), Developer3)

Formulas will auto-fill even if they're not column formulas, so as long as the two rows above a new row have the formula, it will populate into the next row. See: Use or Override Automatic Formatting and Formula Autofill

Cheers,

Genevieve

Genevieve P.

Hi @David Acord

There currently isn't a way to lock a reference to "the row below" and then apply it as a column formula to automatically populate with every new row. That said, we could build something out to manually reference the row number below, and then if you drag-fill the formula down the column it will auto-update the row number to be one below for each row.

For example, I can put this in row 2:

=IF(CONTAINS("OLT", [Project Name]@row), Developer3)

You'll notice that I use @row for the Project Name because I'm looking in the current row for "OLT", then I use 3 after the Developer to reference the row below this current one.

If you want to check the row below for "OLT", you can change the @row to the row number below the one you're working with as well:

If you need both criteria to be true (the current row says OLT and the one below says OLT, too), then try this:

=IF(AND(CONTAINS("OLT", [Project Name]@row), CONTAINS("OLT", [Project Name]3)), Developer3)

Formulas will auto-fill even if they're not column formulas, so as long as the two rows above a new row have the formula, it will populate into the next row. See: Use or Override Automatic Formatting and Formula Autofill

Cheers,

Genevieve

David Acord

Thanks Genevieve! Apologies for the slow response with this - I've been thrown a few more projects which have me also scratching my head! I appreciate your response.