A
Arvi Laanemets
Hi
I have created a workbook, which collects data from several other workbooks,
and displays a chart based on tis info. General setup is next:
There is a hidden sheet (Links) with links to source workbooks - a
predetermined number for every source workbook (department).
On another sheet (Main), a department is selected, and data for selected
department is displayed in a table.
On chart sheet (Chart), a chart based on data in sheet Main is displayed.
The chart is in no way connected to Links sheet directly - it gots all data
from Main sheet - dynamic named ranges were used originally {there is a
SetUp sheet, where the user can determine, how much rows (weeks) are
displayed on Main sheet, and for how many weeks data of them are displayed
on chart}.
It all worked nicely, until I was asked to add an additional department. I
added additional rows into middle of table in Links sheet, redefined all
named ranges, etc. - after that I discovered, that all works fine except for
one department, the one before which on Links sheet were added rows for new
department. For this one department, all named ranges are working properly,
the data for this department are displayed in Main table, etc., but ... the
chart remains empty. When I check 'Show data table', the table contains only
0's, except X-axis labels and series names.
When I select series values in source data dialog, the proper range on Main
sheet is displayed for all series - with all figures existing there. I tried
to dubblicate series using another names, and set series references to new
ranges - the same result. I then defined series as common references (like
Main!$D$6:$D$17) - and again no data reached the chart. I entered links to
those ranges to right of Main table - all figures for all rows were
displayed properly, so ranges were realy all OK too.
The strange thing is, that chart doesn't know anything about different
departments - it refers to some number (determined in SetUp) of rows in
various columns in Main table (data series, X-axis labels), or to some fixed
cells on Main sheet (table header - btw. the table header for all
departments are get properly) or on SetUp (series names) only. When another
department is selected, the values in cells do change, but ranges/cell
references remain same. But for one department it doesn't work on chart!
It seems I'm at end of my wits - and it will be a huge work to redesign the
workbook anew. Maybe somebody has some good advice ready for me!
Thanks in advance!
I have created a workbook, which collects data from several other workbooks,
and displays a chart based on tis info. General setup is next:
There is a hidden sheet (Links) with links to source workbooks - a
predetermined number for every source workbook (department).
On another sheet (Main), a department is selected, and data for selected
department is displayed in a table.
On chart sheet (Chart), a chart based on data in sheet Main is displayed.
The chart is in no way connected to Links sheet directly - it gots all data
from Main sheet - dynamic named ranges were used originally {there is a
SetUp sheet, where the user can determine, how much rows (weeks) are
displayed on Main sheet, and for how many weeks data of them are displayed
on chart}.
It all worked nicely, until I was asked to add an additional department. I
added additional rows into middle of table in Links sheet, redefined all
named ranges, etc. - after that I discovered, that all works fine except for
one department, the one before which on Links sheet were added rows for new
department. For this one department, all named ranges are working properly,
the data for this department are displayed in Main table, etc., but ... the
chart remains empty. When I check 'Show data table', the table contains only
0's, except X-axis labels and series names.
When I select series values in source data dialog, the proper range on Main
sheet is displayed for all series - with all figures existing there. I tried
to dubblicate series using another names, and set series references to new
ranges - the same result. I then defined series as common references (like
Main!$D$6:$D$17) - and again no data reached the chart. I entered links to
those ranges to right of Main table - all figures for all rows were
displayed properly, so ranges were realy all OK too.
The strange thing is, that chart doesn't know anything about different
departments - it refers to some number (determined in SetUp) of rows in
various columns in Main table (data series, X-axis labels), or to some fixed
cells on Main sheet (table header - btw. the table header for all
departments are get properly) or on SetUp (series names) only. When another
department is selected, the values in cells do change, but ranges/cell
references remain same. But for one department it doesn't work on chart!
It seems I'm at end of my wits - and it will be a huge work to redesign the
workbook anew. Maybe somebody has some good advice ready for me!
Thanks in advance!