Convert to a Column Formula Absolute Reference work around

Options

Cannot convert to a column formula (think) because if the absolute refence.

100s/1000s of rows will be pasted into the "To Merge" fields an I can't depend on the user to drag the formula down

Is there a work around?

=IF([To Merge 1]@row <> "", $[Change Values to Questions Asked]$1 + ": - " + [To Merge 1]@row + CHAR(10), "") 

+ IF([To Merge 2]@row <> "", $[Change Values to Questions Asked]$2 + ": - " + [To Merge 2]@row + CHAR(10), "") 

+ IF([To Merge 3]@row <> "", $[Change Values to Questions Asked]$3 + ": - " + [To Merge 3]@row + CHAR(10), "") 

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Stavros_McGillicuddy

    I hope you're well and safe!

    Yes, and no. It's not working because of the specific row reference.

    How are you using the Absolute Reference?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Stavros_McGillicuddy 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Detrie Zacharias
    Detrie Zacharias ✭✭✭✭✭
    Options

    Thanks ..

    HERE is a link to a sample sheet.

    It seems I cannot reference the column names in a formula so< instead I am..

    Concatenating populated "To Merge xx" cells with the values in the "Change Values to Questions Asked" Columns

    You will see the absolute references in the Concatenated Values column (the one I'd like to convert to a column formula)

  • djorg
    djorg
    edited 08/14/22
    Options

    Here is a workaround that seems to be functioning correctly for me on this:

    1. Structure your sheet so all data is nested beneath 1 main parent row. Make it a title block or something like that. There can be multiple nested layers beneath the main parent row if you like.
    2. Create specific columns for each of the absolute values you'd like to reference
    3. In the main top level parent row, input the absolute value references with 1 value in each column of the parent row.
    4. Then in your column formula you can use the formula =INDEX(ANCESTORS([WHAT-YOU-CALLED-THE-ABSOLUTE-VALUE-COLUMN]@row,1). The , 1 in the formula is the important part, as it will take the 1st index of the ancestors array and if you convert this to a column formula you should see it fill you value into every every row in the column except the main parent row where it will show an error.
    5. You can clean this up with an IF(ISERROR() statement and incorporate the +INDEX(ANCESTORS([WHAT-YOU-CALLED-THE-ABSOLUTE-VALUE-COLUMN]@row,1) part of the formula into your column formula how every you like.
  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    @Detrie Zacharias sheet summary fields work in column formulas, would that work?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!