Data shuttle- keeping formulas from first row when doing a complete write over of all rows
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)))
Answers
-
Can you use a Column formula? As long as you are just overwritting cell data with Data shuttle.. The column formulas should stick.
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives