Replacing sheet data regularly to update dashboard

Geraldine Ahsue

Hi all,

How can I replace large swaths of data in a sheet regularly, and preserve its sheet summary functions so that the dashboard will reflect the new data? Is there a way to import an excel sheet directly into a preexisting sheet? Cut and paste is not an option as there is too much data.

Thank you for any insight or advice you can share!

Geraldine

Paul H

Look into the Data Shuttle add-on.

Andrée Starå

Hi @Geraldine Ahsue

I hope you're well and safe!

The best option would be the Premium App, Data Shuttle, but if that's not an option, there might be a way to develop a so-called Import Solution.

More info: 

https://www.smartsheet.com/marketplace/premium-apps/data-uploader

Would that work/help?

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!

Geraldine Ahsue

Data Shuttle is definitely part of my solution. Thank you!

Any suggestions for how to then maintain the summary functions attached to the data? I basically want my dashboard to automatically update anytime I import a new sheet. The sheet has the exact same columns, just different number of rows.

Paul H

@Geraldine Ahsue

Any formulas in the sheet summary and any additional columns that use column formulas will persist when the data is replaced or merged.

I have many Datashuttle workflows running on daily schedules, updating multiple dashboards without any interaction from me.

Andrée Starå

@Geraldine Ahsue

Excellent!

Happy to help!

Yes. The Sheet Summary will be intact as long as you reference the whole columns.

✅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!

Geraldine Ahsue

Thank you for your help! I'm fairly new to Smartsheets so I appreciate your support.

@Paul H that's what I'm going for! Automated dashboard update with little interaction from me. Question: do your formulas formulas persist even when the number of rows change as well?

Andrée Starå

@Geraldine Ahsue

You're more than welcome!

Yes, the formulas in the Sheet Summary will persist if you reference the whole columns.

Would that work?

✅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!

Geraldine Ahsue

Hello!

Oh, are you saying I can build a formula using an entire column, instead of a range?

This is an example of one of my formulas, that I'm using in Sheet Summary:

=SUMIFS([Hard Credit]1:[Hard Credit]1069, [Record Date]1:[Record Date]1069, <=DATE(2021, 6, 30), [Record Date]1:[Record Date]1069, >=DATE(2020, 7, 1))

My issue is that the number of rows will constantly change with the data shuttle, and I don't want to change these formulas every time. Can I make it so that the ranges are entire columns, instead of those specific ranges? Eg. the entire columns of "Hard Credit" and "Record Date"?

If so, that would be incredible!

Thank you,

Geraldine

Andrée Starå

@Geraldine Ahsue

You're more than welcome!

Yes.

Try something like this.

=
SUMIFS([Hard Credit]:[Hard Credit], [Record Date]:[Record Date], 
<=DATE(2021, 6, 30), [Record Date]:[Record Date], >=DATE(2020, 7, 1))

✅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!

Geraldine Ahsue

It works! And it's amazing! Thank you!

Andrée Starå

@Geraldine Ahsue

Excellent!

I'm always 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!