Changing Columns In a Report

David Dolch

Has anybody developed a way to automatically update/change the columns that are displayed in a report. This is what I am working with. I have a sheet with 52 columns, each representing a week of the year. I want to be able to build a report that shows a selected 5 columns of this sheet, in essence a 5 week roll. I can tag the columns I want to display in a row in that sheet, but that is where I get stuck. I cannot use a separate sheet that pulls over the tagged columns with a INDEX-MATCH call because the number of rows on the master sheet constantly changes (child items are added frequently). I guess I could do something with helper columns and an INDEX MATCH on the main sheet, but before I begin to figure that out, has anybody come up with a simpler way to accomplish this?

Thanks

Kerry St. Thomas

You MIGHT be able to use an intermediary if you use this trick I used. (It's a super UGLY trick and I definitely don't recommend it if you can avoid it.)

On this intermediary sheet, name the columns Week01 — Week02 — and so on. Something NOT dated. In one row, use "Record a Date" automation in one cell - and then use that cell to compute the Mondays of This Week, Last Week, and so on for your generically named columns. To fit your specific use case, you mgiht need to convert this into YYYYMMDD format, or MM/DD only or whatever it may be.

Once you've got that, you can now use that Index/Match combination to pull your data from your source sheet on over to the final sheet. Consider adding a checkbox column or something along those lines to allow you to exclude those "helper rows" to get where you need to go - and then create a report that filters the checked rows on that new column.

A better way would be to restructure the underlying sheet - instead of spreading the data out on a column per week, collapse it into ONE column "Week of" that you can put guardrails on using Dropdown format or something like that. (Essentially, I'm recommending you unpivot your underlying data to remove the time intelligence category.)

Good luck!!!

David Dolch

https://community.smartsheet.com/discussion/comment/464638#Comment_464638

I went through something like that, I just didn't break it onto an other sheet. I did it on the same sheet and just created 5 additional columns that would pull in the five "rolling" columns and then I could report on those five.

Then I realized my error. The whole point I want this in a report is because I need users to be able to UPDATE these field. They report their hours using this tool. So after I did all of this magic to get the rolling columns into a report I realized it was basically useless for my needs since they cannot update the actual column, it would be updating the 5 additional columns, which are actually the INDEX/MATCH formulas.

As I said before, I can easily tag the 5 columns I need automatically. Each week it rolls through and labels them 1,2,3,4, and 5 in the first row (columns I don't want to display are left blank). I just need to figure out a way to key off of those rows. I guess I could use the API and write a script to pull the column ID where the value is 1,2,3,4 and 5 and then use an other script to update the report - but there are about 40 reports, so that is a LOT of scripting. Hence why I was hoping there was an easy way to do this, but I am beginning to think that one does not exist.

Thanks for your help though.

Kerry St. Thomas

Yeah. Automatically changing ROWS in a report is super simple - just identify the filter. Automatically changing COLUMNS in a report …isn't a thing, at least that I was ever able to discover.