How to automate monthly data from another sheet?

Fahad Haqqi

Hi all,

Every month, we are required to display new data for review. The problem is that it has to be manually done. I have to login the 1st of the month into the data sheet, copy the data of information that we get from a master sheet, create a new column and paste it there which then is linked to a dashboard with a graph showing the trend.

I wanted to know if there's a solution to something like this where I won't have to manually enter data and it can automatically create a column with the new month title and paste the info there so the graph is updated automatically. Is there such a solution available? Or is there a better way to go around this?

Thanks for the help!

Here's an example of how I do it.

(The data here is the same because it was just a demo for this question)

Paul McGuinness

Hi @Fahad Haqqi

Its possible within a standard smartsheet set up if you can move away from column data, row data can still be graphed but may not show quite the same.

You use two sheets one will be the active calculation sheet pulling that months data and copying it to the 2nd (fixed) sheet on the last day of the month in the evening.

This is how it would look using your data, the active calculation sheet only contains one row.

The formulas for the date setting columns are fairly straightforward

Mth

=MONTH(TODAY())

Year

=YEAR(TODAY())

Month - this would form your graph headers etc.

=IF(Mth@row = "", "", IF(Mth@row = 1, "Jan", IF(Mth@row = 2, "Feb", IF(Mth@row = 3, "Mar", IF(Mth@row = 4, "Apr", IF(Mth@row = 5, "May", IF(Mth@row = 6, "Jun", IF(Mth@row = 7, "Jul", IF(Mth@row = 8, "Aug", IF(Mth@row = 9, "Sep", IF(Mth@row = 10, "Oct", IF(Mth@row = 11, "Nov", IF(Mth@row = 12, "Dec", "Error")))))))))))) + " " + RIGHT(YEAR(TODAY()), 2))

Show (Set as a Column formula on both sheets) ages the row so that a rolling period is pulled into any reports etc. You can set a report to show only rows where this figure is less than 180, i.e. 6 months etc.

=TODAY() - DATE(Year@row, Mth@row, 1)

As shown on the fixed data sheet too

-------------------------------------------------------------------------------------------------------------------------------

PC (Primary Column) Text cell contains 'Move' and used as the element that triggers the move row each month.

-------------------------------------------------------------------------------------------------------------------------------

You would then create a report of both sheets pulling in the information as required, when graphing on dashboards you can play around with the settings and swap the column / row data.

Thats how I have done it, hope that helps

Thanks

Paul

Paul McGuinness

Hi @Fahad Haqqi

Its possible within a standard smartsheet set up if you can move away from column data, row data can still be graphed but may not show quite the same.

You use two sheets one will be the active calculation sheet pulling that months data and copying it to the 2nd (fixed) sheet on the last day of the month in the evening.

This is how it would look using your data, the active calculation sheet only contains one row.

The formulas for the date setting columns are fairly straightforward

Mth

=MONTH(TODAY())

Year

=YEAR(TODAY())

Month - this would form your graph headers etc.

=IF(Mth@row = "", "", IF(Mth@row = 1, "Jan", IF(Mth@row = 2, "Feb", IF(Mth@row = 3, "Mar", IF(Mth@row = 4, "Apr", IF(Mth@row = 5, "May", IF(Mth@row = 6, "Jun", IF(Mth@row = 7, "Jul", IF(Mth@row = 8, "Aug", IF(Mth@row = 9, "Sep", IF(Mth@row = 10, "Oct", IF(Mth@row = 11, "Nov", IF(Mth@row = 12, "Dec", "Error")))))))))))) + " " + RIGHT(YEAR(TODAY()), 2))

Show (Set as a Column formula on both sheets) ages the row so that a rolling period is pulled into any reports etc. You can set a report to show only rows where this figure is less than 180, i.e. 6 months etc.

=TODAY() - DATE(Year@row, Mth@row, 1)

As shown on the fixed data sheet too

-------------------------------------------------------------------------------------------------------------------------------

PC (Primary Column) Text cell contains 'Move' and used as the element that triggers the move row each month.

-------------------------------------------------------------------------------------------------------------------------------

You would then create a report of both sheets pulling in the information as required, when graphing on dashboards you can play around with the settings and swap the column / row data.

Thats how I have done it, hope that helps

Thanks

Paul

Fahad Haqqi

Hi Paul, this is great! I'm going to try implementing this into my sheet. Thanks for your support I appreciate it very much!

Fahad Haqqi

Hi Paul, I am almost done implementing this into my sheet. I wanted to ask how is "Move" in the Primary Column used as an element to automate moving a row? Is there a function that I'm missing?