Column linking: column "expansion" when rows gets added in source sheet
I have a Smartsheet where orders are listed, with a bunch of column with all the information.
I need to do some calculations based on the orders, for which I need several (30+) helpers columns (automatic scheduling optimization).
I find it easier (and cleaner) to have a separate sheet where i copy relevant information of an order (order#, quantity, priority - not the full row), run my "magic", calculate the final sequencing, and then link back in the main sheet just this last parameter.
My problem is that the order list grows, as new rows are added to the bottom of the sheet. In Excel I would be able to create a link in the destination sheet for the entire column, including empty cells at the bottom, so that the two lists "grow together". And I could also preemptively create formulas in the rows that have empty order data, so that when they get populated I automatically get the calculated result.
How can I do the same in Smartsheet? How can I link empty cells at the bottom of a smartsheet so that when they are filled I get automatically the data.
To recap: I want to fill rows at the bottom of a smartsheet, then in another smartstheet I should get a subset of this row (just the cells I'm interested to) added at the bottom and the formulas in the existing columns should use the new data to automatically calculate my values.
I am also considering Workflow automation: copying rows to the bottom of the "working" smartsheet, including data I don't care about, but how to extend the formulas to the new rows?
Thanks and regards