I need to pull data from sheet on a monthly basis.
What is the best approach? I have set up sheet summaries with the data I need pulled into categories and I'm using these summaries in a report, but I need to separate this data based on dates. I am stumped as to how to get what I need.
Answers
-
You are going to need a separate sheet instead of Sheet Summary fields. Then you would set up a copy row automation that copies the rows to a third sheet on a monthly basis to capture that historical data.
-
@Paul Newcome thank you, I will try this.
-
@Paul Newcome I'm not sure this will work for me. I need my data sorted out by the variables that are currently sorted out by the summaries. The last variable is the date and I can't figure out how to keep what I have and break it down even further by month.
For example: I have SF of jobs broken down by designer then by section of the job then by section of the job done in house and by section of the job outsourced. The last breakdown is into month data.
I can do this manually each month with no issue, but I want it to auto populate this data as the jobs move through the system.
-
-
The picture of the sheet columns is representative of the columns and data in the sheet that I am pulling to compile the information in the summary picture.
My issue is that I need to tie this information by date. As you can see in the sheet summary, I have broken it down by person and total SF that currently is "all time data" where I need to separate this data into monthly data.
There are other columns that I am using to compile the data in the summary but I'm not sure how much I can share.
I have also included a sample of a couple of the formulas in my summary so you can see the various places I am pulling data.
Sheet columns
Sheet summary info
Formula Examples
-
My apologies. I thought you were calculating those total totals and trying to show a trend of what the totals were each month.
If you basically need to filter your SUMIFS by date for a specific month/year, you will need to include another range/criteria set along the lines of:
=SUMIFS(........................................., [Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
Change the 1 to whichever month you need and the 2023 to whichever year you need.
-
Would this be a manual process each month to pull the previous month? I was looking more for an auto report that I could pull up and report to management on demand. I also need to evaluate the current data for scheduling purposes.
My ideal situation would be a report on each category, SF OS, SF In House, etc., that I could sort by month and report how much each person has in each category but also know how much they currently have scheduled to when the capacity has been reached.
I am all about automation and pulling manual reports on this is not feasible in the industry we are in and the time constraints on our timeline.
-
Can you provide a mocked up screenshot of the expected output? I initially misunderstood your ask, but then you mentioned you needed it by month. Now it sounds like you need the month/year to be dynamic and show counts for just the previous month?
-
Yes, I'm sorry for the confusion at first on my problem. I didn't realize I could share screenshots :( Without showing my data and what I have, it was hard to explain.
I just spoke with one of my colleagues and we worked on one of the reports I had created to try to get this. Turns out I was close with the report I created trying to find a solution. So the summaries I've created may be a moot point, now.
I was trying to develop a reporting method that contained all of the data needed that I could then sort into reports, similar to what I would do with excel. It seems as though the most simple way for me to do this, is to create these individual reports for each category instead and use the features at the top to manipulate the data.
This still requires me to manually go into the summary on the report to pull the data for the month. While this is not ideal, I have a deadline to provide something to management and I have now spent 3.5 days on this because of my limited knowledge of Smartsheet functions.
@Paul Newcome I appreciate all of your time in helping me. I am new to Smartsheet and am having a hard time determining what features and functions to use to do the things I would normally be able to do in excel within one sheet where my data is contained or even across multiple sheets.
This solution seems so simple. It still doesn't do exactly what I wanted, but will have to be good enough for now.
-
Smartsheet has so many different features, there is definitely a learning curve trying to figure out what suits your specific needs the best.
There could be a number of ways to make your report filter relatively "dynamic" (typically only a helper column or maybe two and a formula and maybe an automation), so if you would like to revisit this to try to get it exactly how you want it, feel free to let me know.
-
@Paul Newcome I know this is a long dead thread but it seems this is the only place I've seen my question elaborated in such detail. I need to develop a way to summarize certain columns of data on a monthly basis that recurs every month. It would be nice if this data could automatically be exported to a separate sheet from which I could then create a dynamic dashboard to display the figures in a MvM/QvQ manner. I think you were on track to explain a possible answer that would solve this problem. Please let me know what you thought would do it.
-
@Samuel Vrba The best start I can suggest for recording data monthly would be setting up a second sheet as an "archive" type sheet and using copy row automations from the working sheet. You can then use different formulas and filters to slice and dice the historical data.
