Hi all; I could use some Excel Ninja help here. I have scoured the blog, digested dynamic ranges with offset formulas and tables, and pretty much bled my eyes out in excel for about two days and this website looking for a solution and haven't found it (I'm so close!).
Goal: Create a Graph with two drop downs that displays a variable amount of year over year data along the X-Axis:
1) Select an item
2) Select a time period
Progress: I've created the dropdowns, graph, and linked data tables and they work.
Problem: Despite using the offset function to create a dynamic named range and linking the source data in the table when I select a shorter time period, the x-axis won't update. I believe it has something to do with the formulas in the table; even though the clear cells of data when not in use, I think the dynamic range still detects that there is something going on and doesn't update. How can I fix this so the linked data table can update and the X-Axis on the graph will adjust size based on the amount of time being displayed?
Working File Download Link
http://www.mediafire.com/view/?63xj76d84qv749u
Contains Three Items with three data fields each since 2011. The data has been made anonymous. There are two defined time periods; Full Year and Last 13 weeks. The graph needs to display year over year data for the selected time period.
Goal: Create a Graph with two drop downs that displays a variable amount of year over year data along the X-Axis:
1) Select an item
2) Select a time period
Progress: I've created the dropdowns, graph, and linked data tables and they work.
Problem: Despite using the offset function to create a dynamic named range and linking the source data in the table when I select a shorter time period, the x-axis won't update. I believe it has something to do with the formulas in the table; even though the clear cells of data when not in use, I think the dynamic range still detects that there is something going on and doesn't update. How can I fix this so the linked data table can update and the X-Axis on the graph will adjust size based on the amount of time being displayed?
Working File Download Link
http://www.mediafire.com/view/?63xj76d84qv749u
Contains Three Items with three data fields each since 2011. The data has been made anonymous. There are two defined time periods; Full Year and Last 13 weeks. The graph needs to display year over year data for the selected time period.