If column has star, then return row name under primary column

How do I return the row name under the primary column, if a separate column title "Active Build" has a star symbol in it?


Trying to use this formula in sheet summary to return the current active build.


Thank you!

Best Answer

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    edited 07/08/22 Answer ✓

    @AnonUser1 I didn't realize that you had posted the almost solution. I built it the same way but using true or false. I normally use 1 or 0 as well, but for this one, you will need to use true or false.


    =INDEX([Primary Column]:[Primary Column], MATCH(true, [Active Build]:[Active Build], 0))


    Again, given that you will always only have one marked with a star.

Answers

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    edited 07/08/22

    @AnonUser1 Assuming that you only have to check one value for one row, as this is the only way this can work...


    For your Sheet Summary field, you will need to use an IF() formula to check the status of the "Active Build" cell.

    =IF([active build]15 = 1, [Primary Column]15, "Value if false")


    [active build]15 = 1

    • this will check if the star is there or not. 1 = true and 0 = false.

    "Value if false"

    • change this to whatever you want to display if there is no star on Active Build
  • @Emilio Wright This wouldn't work, but thank you for the suggestion


    I want to scan the entire sheet to determine 1) the "active build" and the "upcoming build"


    The Primary column has a list of all the possible active builds, but only one will be active at a time (as designated by a star under the active build column), and only one of these rows will be the upcoming build (as designated by a star under the upcoming build column).


    I want the sheet summary to tell me, at any given time, what is the "active build" and what is the "upcoming build".


    Is this possible?

  • AnonUser1
    AnonUser1 ✭✭
    edited 07/08/22

    This is what I've come up with but it returns #NO MATCH


    =INDEX([Primary Column]:[Primary Column], MATCH("1", [Active Build]:[Active Build], 0))


    @Paul Newcome, @Andrée Starå would you be able to help?

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    edited 07/08/22 Answer ✓

    @AnonUser1 I didn't realize that you had posted the almost solution. I built it the same way but using true or false. I normally use 1 or 0 as well, but for this one, you will need to use true or false.


    =INDEX([Primary Column]:[Primary Column], MATCH(true, [Active Build]:[Active Build], 0))


    Again, given that you will always only have one marked with a star.

  • I can't believe I was looking at this for so long and the only thing wrong was that I added quotes !!! Thank you @Emilio Wright

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!