Excel2000: Weird chart behaviour
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! |
All times are GMT +1. The time now is 10:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com