• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Chart not updating when data in named range changes

T

TrangN

New Member
I've created a chart that changes as the data in the named range changes. However, this only works when I open the workbook on my computer. When someone else opens the workbook on their computer, the data in the named range changes as the user changes the selection criteria (e.g. if they change the from/to date) but the chart does not change.


Can anyone tell me what I may be doing wrong? I'm using Excel 2007.
 
Hi ,


Can you post the chart Series range definition and the formula you have entered in the Refers To box of the named range ?


Narayan
 
In Name Manager I have:


ChartLabel=OFFSET(Calculations!$D$27,0,0,1,Calculations!$C$13)

ChartData=OFFSET(Calculations!$D$28,0,0,1,Calculations!$C$13)

ChartAvg=OFFSET(Calculations!$D$29,0,0,1,Calculations!$C$13)


Chart data I have:


=SERIES(Calculations!$C$28,'Report for weekly sales meeting.xlsm'!ChartLabel,'Report for weekly sales meeting.xlsm'!ChartData,1)


=SERIES(Calculations!$C$29,'Report for weekly sales meeting.xlsm'!ChartLabel,'Report for weekly sales meeting.xlsm'!ChartAvg,2)


I hope that's what you're after Narayan. This is the first time I've ever use a forum like this so I'm a bit uncertain on terminology :)
 
Hi ,


I think your formulae and definitions are not the problem ; can you ask your colleagues who open this workbook on their computers , to check whether what you have posted above is what they see when they go through the range and series definitions ?


C13 on the Calculations tab should contain a number ; secondly , if it is a matter of dates , are the default date formats on their computers the same as on yours ?


Narayan
 
Just got one of my colleagues to open the workbook again. When they open the workbook, the range definitions are as posted above but the series definitions are blank/empty on the formula bar. The chart that is shown on her screen is the chart from when I last saved the file.


Date formats are the same on both computers.


When my colleagues change the selection criteria, the data in the named ranges recalculates based on the criteria selected. But this data is not filtered through to the chart. Urghh!
 
TrangN


Is the data sourced from a SQL or other data base or network location that your colleagues don't have access to?
 
Narayan - sorry but I can't upload my workbook due to the nature of the information in it.


Hui - the raw data is on another worksheet in the same workbook.


I've just gone around and open the same workbook from different computers and have found that it works on some computers and not on others. We are all working on Excel 2003.

I'm about to go and compare the settings in Excel on the computer that works vs the one that's not working. Hopefully something will come into light.


Thanks,

Trang
 
TangN


In your original post you said you were using Excel 2007


Now you say the others are using Excel 2003


You are either using a new function like Sumifs or you are using a function that in Excel 2003 required the Analysis Toolpack to be installed as an Add-In
 
Hi,


Here's the link to my upload file.


https://skydrive.live.com/redir?resid=85C1FFDA21CCCF48!107&authkey=!AFHQSZXF0Ut9Yyw


Please note that I have changed the range names and file name so it's different to my original post, thinking that there may be something wrong with the name I originally set.


What I have found is that on the computers where the chart does not work properly, when I go to Edit Series, the Series values is =[0]!SalesData. I then edit the series so that it reads:

=SERIES(Calculations!$C$28,'Sales Report.xlsm'!Month,'Sales Report.xlsm'!SalesData,1)

=SERIES(Calculations!$C$29,'Sales Report.xlsm'!Month,'Sales Report.xlsm'!MthlyAvg,2)


The chart works perfectly after this. I then save the file and close Excel altogether, but when I reopen the file on that same computer, the series value reverts back to =[0]!SalesData.


The Excel settings on both the computer that works and the one that doesn't are the same.


Thanks,

Trang
 
Hi Trang ,


I downloaded your file , and everything works correctly.


How are the others ( where this file does not work ) transferring the file ? Copying it via USB / network , or downloading it from any file-sharing website ?


Narayan
 
Hi ,


I came across a similar problem , in response to an earlier question :


http://chandoo.org/forums/topic/dynamic-chart-pivot-table#post-39109


Unfortunately , the OP never came back with either further information or a solution.


Narayan
 
Hi Narayan,


Do you know if Excel "behave" differently depending the operating system of the computer?


We have some computers here that are on a 32-bit and some on 64-bit operating system (mine is on a 32-bit), and what I found is that this particular report of mine does not seem to work on any computer that is on a 64-bit operating system but it works on those that are on a 32-bit operating system. Not sure if I'm off the rail here or not!?


Thanks,

Trang
 
TrangN


I move seamlessly between Excel 64 and Excel 32 bit installs on Windows 7 64 and 32 bit systems all the time


The only time you'll have issues is with UDF's that must be coded slightly differently (1 keyword needs to be added to the Function line) on Excel 64 bit systems


Does your worksheet contain any Macro's or UDF's?

People should get a warning anyway if that is an issue
 
You must log in or register to reply here.
Back
Top