Column formula to reference 1 cell

BdH

I don't know why but I can't seem to get a simple formula to convert to column formula.

I would like to reference a task name in the main project line to each line in the project plan to later on use it in a separate report.

The formula is:

=[Taskname]$1

After trying to convert it to a column formula I get a syntax errror:

" The column formula syntax isn't quite right, see our help article."

I can't find any explanation in the help file. Both columns are "Text/ number" and copying the formula manually seems to work for all cell but I want this to be a fixed formula for new rows as well.

BdH

Sorry, after reading it a second time....

"NOTE: Column formulas cannot refer to cells or ranges with specific row numbers such as with absolute references, specific cell references, or partial-column ranges. Use @row, column references, and cross sheet references instead."

Genevieve P.

Hi @BdH

You're correct! The $1 is what's preventing it from being a column formula.

That said, if you drag-fill the formula down the column it will populate for any new rows added to the sheet (immediately below your current rows) or rows added in between 2 rows with the same formula. See: Use or Override Automatic Formatting and Formula Autofill

Another option to get that value in the cell would be to use a Change Cell Workflow, where when a new row is added the workflow puts that content into the cell (if [Taskname]$1 is a static value).

Cheers!

Genevieve

Andrée Starå

Hi @BdH

I hope you're well and safe!

To add to Genevieve's excellent advice/answer.

• I would recommend adding a Sheet Summary field that references the parent (if it's only one), and then in your column, you'd reference the Sheet Summary field, and then you can make it a column formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

BdH

Sorry, after reading it a second time....

"NOTE: Column formulas cannot refer to cells or ranges with specific row numbers such as with absolute references, specific cell references, or partial-column ranges. Use @row, column references, and cross sheet references instead."

Genevieve P.

Hi @BdH

You're correct! The $1 is what's preventing it from being a column formula.

That said, if you drag-fill the formula down the column it will populate for any new rows added to the sheet (immediately below your current rows) or rows added in between 2 rows with the same formula. See: Use or Override Automatic Formatting and Formula Autofill

Another option to get that value in the cell would be to use a Change Cell Workflow, where when a new row is added the workflow puts that content into the cell (if [Taskname]$1 is a static value).

Cheers!

Genevieve

BdH

@Genevieve P.

Thank you for your reply. You pointed me in the right direction.

BdH

@Genevieve P.

Perhaps you can help on a follow up on this topic. I have added a cell change workflow using the formula:

=parent()

It seems to be working.... sometimes. Some instances it actually puts in a formula, some instances it add it as text '=parent()

Do you know what could cause this?

Andrée Starå

Hi @BdH

I hope you're well and safe!

To add to Genevieve's excellent advice/answer.

• I would recommend adding a Sheet Summary field that references the parent (if it's only one), and then in your column, you'd reference the Sheet Summary field, and then you can make it a column formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Oh, Brilliant @Andrée Starå!

https://media.giphy.com/media/I4wGMXoi2kMDe/giphy.gif

BdH

@Andrée Starå

Works like a charm!

Andrée Starå

@Genevieve P.

Haha! I love that GIF! Thanks!

Andrée Starå

@BdH

Excellent!

Happy to help!

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