Column linking: column "expansion" when rows gets added in source sheet

ANDREA VITAGLIANO

Hello!

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

Andrée Starå

Hi Andrea,

You would use a cross-sheet formula and a VLOOKUP or INDEX/MATCH combination together with a unique identifier. For your use case, it seems like an Auto-Number column on both sheets as the identifier would keep them in ”sync".

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

ANDREA VITAGLIANO

Thanks Andreé.

I had thought about INDEX/MATCH, but there's something I must verify:

If I use, as Range for INDEX, a column in another smartsheet, say Order#:Order#, will this external reference "expand" as the column [Order#] will get more rows? Or the reference will be frozen when it's created, so that the actual reference would be something like Order#1:Order#54...so that if I add row 55 this will not be part of the range where my INDEX is looking in?

I guess I don't know how External Sheet references are created/stored and if they can be dynamical.

Andrée Starå

Happy to help!

If the range is a column, it will expand automatically, but if it's defined it will not.