Ronin Global
  • Home
  • Articles
    • How-To: Smartsheet Help
    • Enhancement Requests: Smartsheet
    • How To: Zapier Help
    • Business Practices
    • Posts by J. Craig Williams
  • Smartsheet
  • Zapier
  • References
    • Color Codes
  • About Rōnin Global
    • About Craig
  • Home
  • / How-To: Smartsheet Help
  • /
  • Smartsheet: Referencing a cell that doesn’t exist yet

Smartsheet: Referencing a cell that doesn’t exist yet

2017-01-09How-To: Smartsheet HelpSmartsheet

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.

reference_nonexistant_cell_01

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.reference_nonexistant_cell_02
  • A new row is added at the bottom of the sheet because we need somewhere to capture that cell that doesn’t exist yet.reference_nonexistant_cell_03

 

  • 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)

reference_nonexistant_cell_04

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.

reference_nonexistant_cell_05

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.

 

 

Comments

Laura 2017-01-12 at 15:34 - Reply

This was helpful! Thank you for taking the time to do this.

    J. Craig Williams 2017-01-12 at 15:41 - Reply

    You are welcome.

Write a Reply or Comment Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Archives

  • May 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • February 2018
  • November 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • July 2016
  • May 2016
  • April 2016

Calendar

January 2017
M T W T F S S
« Dec   Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Categories

  • Articles
  • Business Practices
  • Enhancement Requests: Smartsheet
  • How To: Zapier Help
  • How-To: Smartsheet Help
  • Politics
  • Posts by J. Craig Williams

@2017-2021 Ronin Global | Theme by speakinginvector