Create multiple multiple rows of data using a variable number from another sheet.

Coen

Good morning,

Before asking how, I need to see if this is even possible. Also, allow me to thank you in advance for any feedback you provide.

What I am working with:

2 sheets:

One sheet is a finance tracker - This sheet uses a form to gather purchase information for equipment we purchase and put on display.

The second sheet is an Asset Tracker. - This sheet draws some of it's initial data from the Asset tracker using Bridge.

The Finance tracker intakes purchases one item at a time. This is fine for Computers or monitors. If we purchase 3 of the same model mice, those 3 mice are, currently, on a single row with a combined cost.

I am wondering if it is possible to use a "Quantity" field in the Finance tracker that can then be used to create that number of Row Items in the Asset tracker.

For Example:

One purchase of 3 mice at 15.00 each totaling 45.00 - one row on Finance tracker but the Quantity multiplier of 3 makes that translate into 3 individual rows of data in the Asset tracker. This way I can assign those 3 mice to 3 different eco systems.

Thank you!

-Rob

SoS | Dan Palenchar

Hello @Coen

I believe you could accomplish this with a series of copy row automations in tandem with some formulas and/or automations. The idea I have is to use the "Quantity" you specify to copy rows into the Asset Tracker and update a different column with each copy so that it continues to copy until the "Quantity" specific is equal to the number of times the row has copied.

I have not tried this exact workflow and can't guarantee it would work but I would try something like:

1. Add two columns in Finance Tracker: (a) one that will keep track of the number of times the row has copied (I'm going to call it "AT Copies" for now) and (b) one that will check when "Quantity" is equal to "AT Copies" (I will call this one "Stop Copying")
2. Use form to populate Finance Tracker
3. Add a Copy Row automation in the Finance Tracker that: (a) has a filter in place where "Stop Copying" is unchecked and (b) kicks in every time a Row is added and/or "AT Copies" changes.

The one kicker here is "AT Copies". The easy choice here is to use a lookup formula and count all of the rows in the Asset Tracker that match a unique identifier in the Finance Tracker. However, this might not trigger the automation (sometimes formulas don't do this to avoid circular/infinite loops). A workaround for this would be to chain the Copy Row automation to a number of Change Cell automation that changes the value in "AT Copies" by 1. This would technically be a different automation for every value as it would work by changing "AT Copies" to 1 if it is blank, then to 2 if it is 1, and so on.

You can also use the API, and this is what we typically do for this type of use case.

This video is related to this topic and might be helpful, though it does not answer this specific question comprehensively. https://www.youtube.com/watch?v=Ah1XrCAkhUc&t=410s

Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

Smartsheet Tutorial Videos: schoolofsheets.com/youtube

Andrée Starå

Hi @Coen

I hope you're well and safe!

It's possible, but it's pretty advanced.

I recently developed something similar for a client.

• It creates a selectable number of assignment rows with specific details from a defined start date. Consultants use this to report their work hours and more on a row per date.

What data would need to be included on each row?

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Coen

Good morning, good morning!

Thank you, both, for the suggestions. I will try these out tomorrow as I will be in a training all day today.

Thanks again!!

Andrée Starå

@Coen

Excellent!

Happy to help!

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

JBG

@Coen I am curious if you managed to get this working. I am trying something similar and followed the proposal of @Dan Palenchar but as he commented the automation on the first sheet stops after the first copy. He proposed then to have aa range of automation by updating a cell value which will then trigger another copy.

For my use case this would mean to many cell update automations. The other challenge is that you have more than 5 workflows triggering each other, which is the limit.

Your response would be appreciated.

Regards

Coen

@JBG Good morning and happy New year. I never got this working, unfortunately I was pivoted into another project before I got to. I think this now has a pin in it. : /

Thank you so much for the help and follow up!