Data shuttle- keeping formulas from first row when doing a complete write over of all rows

Lori Rainery

We are trying to automate a price sheet from an outside system using the data shuttle. We pick up the file from OneSrive and map it to the target sheet in Smartsheet, this is where we are stuck. Is there a way to keep the crazy formulas we have created for all the tiers and discounts without having to manually log back into the sheet and copy/paste them from a notepad? We looked into expressions and it seems to ignore us, lol / Any advice would be greatly appreciated. Thank you!

An example of one of the formulas: =IF(AND($[Partner Type]$1 = "MSP", [MSRP (USD)]@row > 0), ([MSRP (USD)]@row * (1 - $[MSP Discount]$1)), IF(AND($[Partner Type]$1 = "VAR", [MSRP (USD)]@row > 0), ([MSRP (USD)]@row * (1 - $[VAR Discount]$1)), IF(([MSRP (USD)]@row = 0), "Custom Pricing Can Be Arranged Through Your Sales Representative", 0)))

Darren Mullen

Can you use a Column formula? As long as you are just overwritting cell data with Data shuttle.. The column formulas should stick.

Lori Rainery

Thank you @Darren Mullen , the team and I thought of that as well but for the life of us, we can't seem to get them to work as column formulas. I think it has to do with this:

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.

We do refer to specific and absolutes to get it to work the way we needed it to.