Cell Reference -1?

Jezza

I'm trying to reference a cell in another row and then have the ability to copy that down a column so everything is showing the same result.

I have a sheet with study data (lets say 20-40 rows of data with a single row I'm using as a header row with the project/study name) and I copy/paste these sections over and over as new studies are added and each is updated accordingly.

I have a column I am using in an export to excel for pivot chart that I need the study name from the header row copied

I know I can use:

=$[Task / Activity]$row

however I would like to use =$[Task / Activity]$row-1 to reference the cell in the row above as this would be much easier to copy/paste without having to change the row # reference in each formula

Not sure if this is doable or not.

Any insight is appreciated!

Nick Korna

Hi @Jezza,

It should be possible, with a helper column. Add in a helper column such as the Row ID (this can be autonumbered starting at 1) and from there you can use an INDEX/MATCH to grab the Task/Activity name from the row before:

=IF([Row ID]@row > 1, INDEX([Task/Activity]:[Task/Activity], MATCH([Row ID]@row, [Row ID]:[Row ID]) - 1), "")

Sample output:

Hopefully this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂

Jezza

Hi, let me know if this looks correct, I'm getting a circular reference error. Note, I moved the study name that I want to copy, ([Study Name]@row1) in this screenshot, into its own column as I am eventually outputting this into excel so I can pivot it out.

In this screenshot you can see the two black rows, those are the breaks for new/different studies - going forward to create a new study users will essentially copy/paste rows 1-34 and I need the formulas to work each time, with the user only needing to update the study name in the black highlighted row - hope that helps?

Nick Korna

If your studies are taking the name from the parent (black coloured) rows, then you can delete the Row ID. You'll still need an extra column for the study name, but can use this instead:

=IF(COUNT(CHILDREN()) > 0, [Study Name (Parent)]@row, PARENT([Study Name (Parent)]@row))

Where the Study Name (Parent) would the value to modify and the Study Name would be what you can use to pivot in Excel. The middle column's function would be covered by your other columns in your sheet.

You won't be able to use the formula in the column itself as it would obviously cause a circular issue similar to yours, but this is a workaround to get the desired outcome.

Jezza

Perfect, thanks so much, works as I needed