Autofill Cells When New Rows Added

kelceyg

Hello!

I am trying to figure out how I can autofill a column in a RAID Log sheet based on the project name entered in a cell in the Project Plan sheet.

What I tried:

First, in the RIAD Log I added a "Project Name Helper" column and linked it to the cell that has the project name in the Project Plan sheet. I then added a second column, "Project Name", and tried =[Project Plan Helper]$1. I then tried to convert it to a column formula, but received a pop-up that my syntax isn't right.

I then tried =IF([Project Plan Helper]1 <> " ", [Project Plan Helper]1, " "). Received the same syntax error when I tried to convert to a column formula.

I tried adding $ to the column name and row number, but that made no difference.

The reason I only want it to fill in the Project Name column when new rows are added is because we have an Auto Number column, so I don't want to mess anything up by prefilling in the column. Also, the number of RAID Log items will vary from project to project, so I don't want to have to be checking everyone's sheets to make sure the Project Name column is being filled out.

Paul Newcome

It is because of the direct row reference and "locking" references with the $. Column formulas must use "@row" for the "row number" and cannot have "locked" cell references using the "$".

There are two ways you can do this. My preferred method is to create the cell link in a sheet summary field and then reference the sheet summary field as your column formula.

=[Sheet Summary Field Name]#

The catch... You cannot directly create a cell link in a sheet summary field. You can however create cross sheet references. So start as if you are going to write a formula with a cross sheet reference. The function itself doesn't matter because we are going to eventually get rid of it.

=SUM(

Now create a cross sheet reference following the usual steps but only to that single cell you want to pull over. Once you "Insert Reference", you will have the expected

=SUM({Cross Sheet Reference Name}

Now just delete the SUM function so you are left with

={Cross Sheet Reference Name}

And there you go. Just reference the sheet summary field as mentioned earlier, and you should be all set.

If you prefer to not use the sheet summary field, you can follow those same steps to create the

={Cross Sheet Reference name}

and then just apply that as your column formula. I don't prefer that particular method though as now you have the cross sheet reference being pulled into every single row instead of just the one field which can slow things down on larger / more complex sheets.

Paul Newcome

It is because of the direct row reference and "locking" references with the $. Column formulas must use "@row" for the "row number" and cannot have "locked" cell references using the "$".

There are two ways you can do this. My preferred method is to create the cell link in a sheet summary field and then reference the sheet summary field as your column formula.

=[Sheet Summary Field Name]#

The catch... You cannot directly create a cell link in a sheet summary field. You can however create cross sheet references. So start as if you are going to write a formula with a cross sheet reference. The function itself doesn't matter because we are going to eventually get rid of it.

=SUM(

Now create a cross sheet reference following the usual steps but only to that single cell you want to pull over. Once you "Insert Reference", you will have the expected

=SUM({Cross Sheet Reference Name}

Now just delete the SUM function so you are left with

={Cross Sheet Reference Name}

And there you go. Just reference the sheet summary field as mentioned earlier, and you should be all set.

If you prefer to not use the sheet summary field, you can follow those same steps to create the

={Cross Sheet Reference name}

and then just apply that as your column formula. I don't prefer that particular method though as now you have the cross sheet reference being pulled into every single row instead of just the one field which can slow things down on larger / more complex sheets.

KPH

Hi @kelceyg

You can't refer to a specific cell (either with a row number or $ row number) in a column formula.

You could enter the project name as a field on the sheet summary and then reference that field in your formula. That formula could be a column formula.

To add the name to the sheet summary click the icon on the right menu and + New Field:

To refer to it in your sheet enter the formula

=[name of field you created]#

where the part in bold is the name of the field you added.

Hope this works out for you!

kelceyg

Using the Sheet Summary field worked out great! Thank you!

KPH

That's great! Glad you have it working.

Paul Newcome

Happy to help. 👍️