I would like to reference a cell using the column name & a number from another cell for the row #

Joel Eldredge
Joel Eldredge ✭✭
in Formulas and Functions

I am using Predecessors in my sheet and I am trying to figure out a way to see if the predecessors has been finished based off a check box.

cell refrence.JPG

Is there a way to make something like this to work? =IF(FinishedPredecessors@row = 1, 1, 0)

I would like to take the number from the predecessor cell and use it to reference which row I am checking to see if it is finished.

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Joel Eldredge

    I've figured out a way to do this with an INDEX function, however you'll need an additional Helper Column inserted in your sheet. You can hide this column so it doesn't show, and just use it for the formula.

    The Predecessor column formats values a little differently as it's used in Project Settings. This means that the numbers aren't shown as numerical values (you'll notice they're on the left side of the cell instead of the right). The Helper Column will help us convert that number into a value.

    Insert a column and simply put in this formula:

    =Predecessors@row

    Then turn this into a Column Formula (and hide it on your sheet).

    Now we can use the VALUE function wrapped around that helper number to turn it into a value. This will enable us to use it as a Row Index within the INDEX function, like so:

    INDEX(Finished:Finished, VALUE([Helper Column]@row))

    This returns the cell content in the Finished column for the row specified in the Predecessor column (translated by your Helper).


    Then we can embed this into your IF statement:

    =IF(INDEX(Finished:Finished, VALUE([Helper Column]@row)) = 1, 1, 0)


    Keep in mind that if you have a blank cell in the Predecessor column you'll receive a formula error. You can fix this by adding an IFERROR statement around the whole thing:

    =IFERROR(IF(INDEX(Finished:Finished, VALUE([Helper Column]@row)) = 1, 1, 0), "")


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Joel Eldredge

    I've figured out a way to do this with an INDEX function, however you'll need an additional Helper Column inserted in your sheet. You can hide this column so it doesn't show, and just use it for the formula.

    The Predecessor column formats values a little differently as it's used in Project Settings. This means that the numbers aren't shown as numerical values (you'll notice they're on the left side of the cell instead of the right). The Helper Column will help us convert that number into a value.

    Insert a column and simply put in this formula:

    =Predecessors@row

    Then turn this into a Column Formula (and hide it on your sheet).

    Now we can use the VALUE function wrapped around that helper number to turn it into a value. This will enable us to use it as a Row Index within the INDEX function, like so:

    INDEX(Finished:Finished, VALUE([Helper Column]@row))

    This returns the cell content in the Finished column for the row specified in the Predecessor column (translated by your Helper).


    Then we can embed this into your IF statement:

    =IF(INDEX(Finished:Finished, VALUE([Helper Column]@row)) = 1, 1, 0)


    Keep in mind that if you have a blank cell in the Predecessor column you'll receive a formula error. You can fix this by adding an IFERROR statement around the whole thing:

    =IFERROR(IF(INDEX(Finished:Finished, VALUE([Helper Column]@row)) = 1, 1, 0), "")


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Joel Eldredge
    Joel Eldredge ✭✭

    Thank you so much! This was supper helpful and solved the issue we were having. We have been using it over a week now.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Hi @Joel Eldredge

    I'm happy I could help! I've been working on another post and realized that this formula doesn't take into account if you ever have Lead or Lag days (Ex, if the Predecessor column says: "3FS + 3d").

    We can adjust your formula to look for a + symbol to see if the Predecessor is more than just a number. I'll break the additional statement and you can add it if you think it will help with your project.


    If the Helper Column cell in this row contains a + symbol, then only search for the Value in the LEFT side of the cell (the row number) to see if the box is checked.

    IF(AND(CONTAINS("+", [Helper Column]@row), INDEX(Finished:Finished, VALUE(LEFT([Helper Column]@row, 1))) = 1), 1


    Otherwise, (If the number in the Predecessor column is a solo number) run your original formula:

    ... IF(INDEX(Finished:Finished, VALUE(Help@row)) = 1, 1, 0))


    Full formula:

    =IFERROR(IF(AND(CONTAINS("+", [Helper Column]@row), INDEX(Finished:Finished, VALUE(LEFT([Helper Column]@row, 1))) = 1), 1, IF(INDEX(Finished:Finished, VALUE([Helper Column]@row)) = 1, 1, 0)), "")


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!