Copy Row to overwrite formatting/Formula

Ross Novotny

I am trying to create a new sheet from a template in Zapier, (which is working fine). But I am next trying to copy a row from a "Master" sheet to that newly created sheet. It does create the new row, however it is always one row beyond my pre-filled formulas. In the template I have a formula to read information in [Column A]15. The row that gets copied always populates [Column A]16, even though there is no writing in row 15. Is there anyway around that? Even if it's not the method I am use, I would like to have a pre-filled formula (can't be a column formula) that will take into consideration an incoming row that gets copied.

Mike Wilday

Can you delete that blank row? if the formula is auto populating from rows above it, it will auto populate into the newly created row (I imagine form the copy too if that column has no value to overwrite it.

See the following article which has helpful information on autofilling formulas.

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Ross Novotny

When I delete the blank row above the content that I want, it creates an "#Unparseable" issue (=#Ref). Because my formula is referring to the row I just deleted.

Mike Wilday

I'm not sure if what you're trying to do is possible. @Paul Newcome @Andrée Starå thoughts?

Paul Newcome

Can you provide more detail as to what the formula is and what the expected result is? Why can't it be a column formula? Is there a reason you have a formula that references a blank row?

Ross Novotny

Hi @Paul Newcome . I will try to keep the description somewhat concise. I am trying to set up a master sheet for a Lender. Then when they approve the loan on the Master, it will create a sheet specific to that loan. That loan specific sheet will be generated through Zapier based on a template I created in Smartsheet. But once I create the new loan specific sheet, I would like to populate some information from the Master. The template looks like a ledger sheet, but has some loan specific information at the top - address, contact information, remaining balance on the loan, interest accrued. All of these pieces of information (address, contact, etc) are stacked on top of each other therefore can't be a column formula. And even with a slightly different configuration, If I copy a row automatically form the master sheet, I can't have the "address" field for example read a "Hidden Address Field", because the automation will automatically place the copied row information outside of the formula. I can't do a formula in the "address" field like "[Hidden Address Field]:[Hidden Address Field]" because it's a text column. If there was a way to populate the content of the "Hidden Address Field" to all rows, that would be helpful, but I don't think that it's possible to right a formula that just copies the content of one cell into an entire column.

I know that's a lot of information Paul. Any nuggets of wisdom is appreciated.

Paul Newcome

Are you able to provide some screenshots with some mock data?

Ross Novotny

@Paul Newcome below is a screenshot of what is happening. In Step 1 I am creating the columns, and the bulk of the sheet form a template triggered in Zapier. I was able to get the "Primary Contact", "Street Address", etc to populate because of formulas like: =IFERROR(INDEX(COLLECT([Primary Contact]:[Primary Contact], [Primary Contact]:[Primary Contact], <>""), 1), ""). This is reading a hidden column that is populated in the step 2 row, which is being copied from the Master Sheet. That has all the data for the primary contact, street address etc. I added some conditional formatting so that the Step 2 portion would look like it was part of the template in step 1. I am now stuck on step 3.

Here are the challenges. I initially tried to copy over another row that would include the formulas I need in the columns such as "Rolling Balance", however formulas don't copy over. And because I don't have the Row ID of this new template, I can't just "update" a row form Zapier. Automation workflows could have helped if they transferred in a template, but workflows don't copy over. All those avenues seem to be dead ends. So now I am looking to figure out if my rolling balance could be turned into a column formula. This is the current formula: "=IF(AND([Transaction Information]@row = "", [Transaction Type]@row <> ""), SUMIF([Transaction Type]$16:[Transaction Type]@row, "Advance", [Transaction Amount]$16:[Transaction Amount]@row), "")". The formula needs to count everything in the "Transaction Amount" Column up to that row, but no further. I was thinking if there was a way to create an indirect formula that would essentially concatenate two components to create a single cell reference, meaning if I am in row 16, I could have a AutoNumber column that would show "16" and I could have a combined reference of the "Transaction Amount"+"AutoNumber@Row". I haven't figured that one out, but if you think there is a way to make the Rolling balance formula into a column formula, that might be the last hurdle on the monumental effort. Thanks for the help

Mike Wilday

What about using a column formula in your original template? Would that carry across and work for you for those columns?

https://www.smartsheet.com/content-center/product-news/release-notes/column-formulas?iOS=

Ross Novotny

@Mike Wilday The column formulas do carry over, the challenge is I can't (currently) convert my "Rolling Balance" formula into a column formula. You can't have static references in column formulas unfortunately and this formula currently does.

Paul Newcome

When you add new rows, autofill should be grabbing the formulas and pulling them down. Are new rows being added immediately under the last used row? So when you add row 18 for example, are rows 16 and 17 already populated?

Mike Wilday

I think the issue is he needs the new row to reference the Current Balance Field, but he is zapiering in a segment from another sheet before that first row begins.

I am wondering if you can go back to a template and import a row from Zapier that has a first row data that references that column. And pulls that row only from the template and adds it to the bottom of your sheet to initiate the formula? Idk This is really tricky.

Ross Novotny

I think the best I am going to be able to do is adjust the template to have the "ledger" portion included, and include the formulas. Then the row with the data is going to populate at the very bottom of the sheet. I am going to have some conditional formatting where that bottom row with the data will look like a colored row indicating the end of the formulas (and ledger). It's not perfect, but I think it can work given the current Smartsheet/Zapier functionality. I wish the Zapier integration was a little bit more robust. If there was a lookup function and/or if you could update a row without having the Row ID, that would open up all sorts of possibilities.

Mike Wilday

I'm not a developer but I bet you can use API's to do something like you're suggesting.

https://smartsheet-platform.github.io/api-docs/