PaulFogel123
Member
Hello,
In the attached file, I have a report that displays sparklines in column D, and graphs in columns W-AE off to the right. The sparklines reference columns AJ-AU. Columns AN-AU have zeros in them. They'll be filled in as the year progresses. Similarly, the graphs reference the columns for Jul-Oct 2019. If I reference the dates to also include Nov 2019-June 2020, the graph series will be short; most of the graphs will be blank (using na() for zeros).
I would like to have both sparklines and graphs display only the dates for which I have actuals. I don't see how to do this without a lot of manual adjustments, and I've got 75 of these to generate every month.
Dynamic ranges might be useful here. As I add data to the range, the range would change automatically to pick it up. But what if my graph ranges are already set up for the whole year? With sparklines, the dynamic ranges work if the next column (in my example attached, Nov-19) is blank. Nov-19 could have "", or #NA, but it will never be blank—unless I manually add data for Nov-19 and fill in the vacant column. Sparklines interpret any of these as zero and will display zeroes for the remaining months, leaving extra space in the sparkline where I don't want it.
Each month, I'll import the month's actuals into the file through Power Query, where formulas will then pick it up. Since I'm not adding data manually to the tables, the sparkline range has to be predefined as the whole year, but that leaves extra space in the sparkline cell.
What are some options?
Paul
In the attached file, I have a report that displays sparklines in column D, and graphs in columns W-AE off to the right. The sparklines reference columns AJ-AU. Columns AN-AU have zeros in them. They'll be filled in as the year progresses. Similarly, the graphs reference the columns for Jul-Oct 2019. If I reference the dates to also include Nov 2019-June 2020, the graph series will be short; most of the graphs will be blank (using na() for zeros).
I would like to have both sparklines and graphs display only the dates for which I have actuals. I don't see how to do this without a lot of manual adjustments, and I've got 75 of these to generate every month.
Dynamic ranges might be useful here. As I add data to the range, the range would change automatically to pick it up. But what if my graph ranges are already set up for the whole year? With sparklines, the dynamic ranges work if the next column (in my example attached, Nov-19) is blank. Nov-19 could have "", or #NA, but it will never be blank—unless I manually add data for Nov-19 and fill in the vacant column. Sparklines interpret any of these as zero and will display zeroes for the remaining months, leaving extra space in the sparkline where I don't want it.
Each month, I'll import the month's actuals into the file through Power Query, where formulas will then pick it up. Since I'm not adding data manually to the tables, the sparkline range has to be predefined as the whole year, but that leaves extra space in the sparkline cell.
What are some options?
Paul