Convert to a Column Formula Absolute Reference work around

Detrie Zacharias

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), "") 

Andrée Starå

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!

Bassam Khalil

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.

Detrie Zacharias

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

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

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