SYKEMAKAVELI
New Member
- Joined
- Apr 18, 2023
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Hi,
I have a 11 (row) x 15 (column) table in Excel.
Cells A2:A11 are the categorical variables, and cells B1:O1 are time values that I want to have as the x-axis.
Cells B2:O11 are numeric values that I want to use for the stacked chart.
I want to create a stacked bar chart of the data.
I know how to make a stacked bar chart for static data, however the tricky bit here is that there could be blanks in both the categorical variables, or the time values, and I only want the graphs to show the values where there are no blanks.
For the rows, the blanks, if there are any, are always at the end (i.e. no leading blanks). However, for the time values, there can be blanks at either the beginning or end (but not both).
With new data, the location of the blanks can change for both the columns and rows.
I've tried to create a dynamic range name using the filter function to filter out blank values across the wider table, however it doesn't seem to work.
The only remaining idea I have is to use VBA to define the range location based on the first and last non-blank values in the table, and trigger a function to update the graph every time there is a change in the source data.
However, I'd prefer to do this without VBA if possible.
Does anyone have an idea of how I can dynamically create the graph?
Thanks
I have a 11 (row) x 15 (column) table in Excel.
Cells A2:A11 are the categorical variables, and cells B1:O1 are time values that I want to have as the x-axis.
Cells B2:O11 are numeric values that I want to use for the stacked chart.
I want to create a stacked bar chart of the data.
I know how to make a stacked bar chart for static data, however the tricky bit here is that there could be blanks in both the categorical variables, or the time values, and I only want the graphs to show the values where there are no blanks.
For the rows, the blanks, if there are any, are always at the end (i.e. no leading blanks). However, for the time values, there can be blanks at either the beginning or end (but not both).
With new data, the location of the blanks can change for both the columns and rows.
I've tried to create a dynamic range name using the filter function to filter out blank values across the wider table, however it doesn't seem to work.
The only remaining idea I have is to use VBA to define the range location based on the first and last non-blank values in the table, and trigger a function to update the graph every time there is a change in the source data.
However, I'd prefer to do this without VBA if possible.
Does anyone have an idea of how I can dynamically create the graph?
Thanks
