Smartsheet: Referencing a cell that doesn’t exist yet

When I saw the post on the Smartsheet Community, I didn’t think there was an answer.

The question (paraphrased) was:

How do I reference a cell that does not exist yet?

The use case is a sheet is populated via a WebForm and adds new rows to the sheet at the top. A certain column in the sheet contains the key information and somehow, it would be nice if the value that is in that cell could be put somewhere else, automatically.

As I said, first thoughts were “the usual suspects won’t work”.

Like most spreadsheets, Smartsheet allows relative (=ColumnRow) and absolute (=$Column$Row) references. However, once a column or row is added, the reference needs to shift, otherwise every time you add or delete something the references would be wrong.

So how to reference something that doesn’t exist yet?

I tried two other things before I found the solution. I’m pretty sure those two were bugs but I haven’t proven that to myself yet, so haven’t told Smartsheet about them. It is nice that I found the solution on the third try, because … I was approaching this from a “can’t be done” perspective, not a “there’s a solution here and I’ll find it” perspective … and I had only one idea left.

Once I figured it out, the solution is pretty simple. Those are the best kind, aren’t they?

• Add a column all the way to the left of the columns. I named mine [GetCreated], because that’s what it is going to do.

 

• Add a formula to reference the [Created] column’s date on the same row.
• A new row is added at the bottom of the sheet because we need somewhere to capture that cell that doesn’t exist yet.

 

• The new (bottom) row contains two formulas:

In [GetCreated]# cell, the formula should be (where # is the row number)

=MAX(Created:Created)

and in the [Key]# cell, the formula should be (where Key is the column of interest and # is the row number)

=INDEX(GetCreated:Key, 1, MATCH(GetCreated7, GetCreated:Key) + 2)

In my example, the highlighted row is row 7.

And you can see the value in row 7 is the same as row 1 (5512).

If I add a row, like this, the numbers match.

This takes advantage of the MATCH() function returning the first match it finds, so even though all the rows shown were created today, it finds the first one – which is the one we want. That is why this won’t work if the rows are added at the bottom.

But now that I know this has a solution, that probably does too.

The other reason this works is that the first argument of the INDEX() function and the second argument of the MATCH() function are taking a range which I used as the whole columns GetCreated:Key. The whole column is the whole column, regardless of how many rows there are.

One final note on the MATCH function:

    MATCH(what to look for, where to look)

returns a 1 because it finds what is it looking for in the first column, first row of the range. We then add 2 for my example because the Key column is two columns to the right of the GetCreated column.

    MATCH(what to look for, where to look) + 2

And there it is.

If you read this and like it, please let me know.