Create cell reference based upon horizontal axis position

Mike Kavalchuk

Is there a way to create a cell reference based upon a horizontal axis? For example, for a given column, I want it to always represent the cell directly to its left.

For this example scenario, a new column will be added for every date and the new results will be entered into that column - Since the new date column will always be added directly to the left of the "Most Recent Assignment" column, I want the cell reference to dynamically update based upon any new columns that are added.

The purpose of this is to maintain a report that is generated on this column

Note: The structure of the base sheet CANNOT be changed - New dates must be created as columns.

Figure 1 - When creating the reference, it maintains the column

Figure 2 - I want the "Most Recent Assignment" to now reference the 10/26 assignment.

Is this a possibility?

Genevieve P.

Hi @Mike Kavalchuk

I would normally suggest rearranging your sheet to have the IDs as the columns and the Dates as the rows, but you said this isn't a possibility.

There isn't a way to have a formula automatically adjust to reference the column next to it, as columns are added/rearranged on the sheet. You will need to update the formula to have it reference the new column each time a new one is added in.

To make this as simple as possible, you will want to make this "Most Recent Assignment" formula a Column Formula. This will be easy to do since you're using @row. Then when you go to insert the new column, you can Edit the column formula once to reference the new column and it will apply this update to all of the rows in that column. (See Here for more information on Column Formulas).

I hope this helps!

Cheers,

Genevieve