I would like to reference a cell using the column name & a number from another cell for the row #
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.
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.
Best Answer
-
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
Answers
-
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
-
Thank you so much! This was supper helpful and solved the issue we were having. We have been using it over a week now.
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!
