Help with easier way to build dashboard for each year

Hannahsamd

I am building a dashboard that consists of multiple different reports that are all linked to metric sheets. In the formulas of the metric sheets, I have hard coded the year into them but I need to build this dashboard for every year and don't want to have to change the year in every single formula (there are about 5 metric sheets and formula for every month). I want to be able to switch between years easier or just be able to copy all the metric sheets and create the reports easier.

This is one of the metric sheets:

The data for these comes from one large data set in smartsheet. Any advice would be appreciated!!

Paul Newcome

Please see the below series of snippets and let me know if it makes sense.

Paul Newcome

Your saving as new the entire folder. You should have one folder that has all metrics sheets, reports, and the yearly info sheet in it. This folder can have sub-folders if needed, but everything should all be in one "master" or "template" folder.

This one single folder that has everything in it should be saved as new as opposed to each item individually.

Andrée Starå

Hi @Hannahsamd

I hope you're well and safe!

What I usually do in my client solutions is to add the Week, Month, Year, and similar to the sheet summary section, so it's easy to update, + it could give you the option to create column formulas.

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!

Paul Newcome

I usually add sheet summary fields. If I have multiple sheets all referencing the same data point (year for example), I will create a "Yearly Information Sheet" where I can put those types of data points in.

Then in each sheet that needs to reference the year, I will use a Sheet Summary field with a formula/cross sheet reference to pull the year in from the above mentioned "Info" sheet.

This way I can just update it in that one sheet, and all of my metrics sheets will automatically update.

If you need to recreate year over year and want to save historical data, I would drop everything into a folder or workspace (can contain subfolders) and then "Save as new". This will keep the new reports linked to the new metrics sheets, the new metrics sheets linked to the new source sheet(s), and all you would need to do from there is update the data in the single "Info" sheet to push through the new year.

Hannahsamd

@Paul Newcome Thank you! Do you have an example of this? I have already created the sheets so I do not want to mess up when changing all of these.

Hannahsamd

@Andrée Starå Thank you! Do you also have an example of this? I want to make sure i am changing my spreadsheets correctly.

Paul Newcome

Please see the below series of snippets and let me know if it makes sense.

Hannahsamd

@Paul Newcome Thank you! when I change the year in the Yearly Info sheet it doesn't change any of the numbers. I have troubleshooted but can't seem to figure it out. Any advice?

Paul Newcome

Did you use the formula with the cross sheet reference to pull the year into the metrics sheet?

Hannahsamd

@Paul Newcome I got them to change, but when saving as new in order to keep it at 2023 and changing the year to 2022 to make a new report all of the numbers are saving. How do you change the year without changing the save as new ones? I need to save the 2023 and make a new report with the metrics for 2022

Paul Newcome

I'm not sure I follow what you mean. What steps are you taking in what order?

Hannahsamd

@Paul Newcome I set up all of the metric sheets with the summary. I then set the year to 2023 and Saved As New the metric sheets and Reports in order to save them as is. I then changed the year to 2022 to make new metric sheets and reports with the 2022 numbers.

When I change it to 2022, it changes the reports and metric sheets that I saved as new and was hoping to have those saved as new 2023 ones to stay as 2023 numbers. How can I make sure that those numbers do not change? I am trying to do this step in your directions from above: "This will keep the new reports linked to the new metrics sheets, the new metrics sheets linked to the new source sheet(s), and all you would need to do from there is update the data in the single "Info" sheet to push through the new year."

Paul Newcome

When you save as new, make sure everything is in a single folder to include the metrics sheets, reports, AND the yearly info sheet. This will create a new Yearly Info sheet with the save as new that is linked to the new sheets and reports while the original Yearly Info sheet stays linked to the original metrics sheets and reports.

Hannahsamd

@Paul Newcome I saved as new and renamed them into a new sub folder all together. Is this correct?

Paul Newcome

Everything should be all together in the same folder BEFORE you save as new. If that's not the issue, are you able to provide a series of step by step screenshots?

Hannahsamd

@Paul Newcome Do I save as new into a new folder or the same folder?

Hannahsamd