Ronin Global
  • Home
  • Articles
    • How-To: Smartsheet Help
    • Enhancement Requests: Smartsheet
    • How To: Zapier Help
    • Business Practices
    • Posts by J. Craig Williams
  • Smartsheet
  • Simulation
  • 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.

 

 

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.

Recent Posts

  • JADE 5.3.0 JTOPMERET® PSA
  • I Don’t Feel Petulant …
  • Unforced Error – Smartsheet UX change
  • If I have to ask
  • Avoid etc.

Recent Comments

  • Ronin Global on JADE 5.3.0 JTOPMERET® PSA
  • Thomas Roth on JADE 5.3.0 JTOPMERET® PSA
  • Ronin Global on Smartsheet: Set up Update Requests to increase workflow velocity
  • Cristhian on Smartsheet: Set up Update Requests to increase workflow velocity
  • Ronin Global on Smartsheet: Sort by Parent (Ignore Children)

Archives

  • August 2023
  • 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

Categories

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

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

@2017-2023 Ronin Global | Theme by speakinginvector