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!

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

Do you have numerical data in the ranges, or text that looks numerical
to you and me but not to Excel?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Arvi Laanemets wrote:

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!

Hi

At start - I did solve the problem creating the workbook anew.

About old workbook - you did give me a hint which was a worthy one. Data in
Main sheet are calculated as dynamic links to sheet Links (OFFSET from table
in sheet Links). Data to sheet Links are retrieved from other workbooks as
fixed links. All cells in datarange on sheet Links are formatted as General,
and they must inherit the format of linked to cell. For one department, for
some reason I don't remember anymore, I have modified the formula to return
all linked values as strings (="" & MyFormula). (I didn't see it, because
column widrhs were minimized)


Thanks for clearing this case for me.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Jon Peltier" wrote in message
...
Do you have numerical data in the ranges, or text that looks numerical to
you and me but not to Excel?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Arvi Laanemets wrote:

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!