If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Named Ranges in Seperate Workbook
Hi,
I have a Summary workbook that has multiple charts that are copied/linked to different source workbooks. The Source workbook chart is created using dyamic named ranges (e.g. source_xvalues). I am getting odd behavior and don't understand how or what order excel looks at different workbooks. If both the Summary and Source workbooks are open, life is grand. In some cases, if I close the Source, the series disappear from the Summary chart. In other cases, the series don't disappear. Any suggestions as to how to handle this problem? Regards Tim |
#2
|
|||
|
|||
Named Ranges in Seperate Workbook
Tim -
I did a little experiment. I created a chart in one book based on data from another. I made two series, one using a named range, the_Y, that was directly based on a worksheet range, the other that was defined in the Define Name dialog as offset one column from the first range, the_Y2=OFFSET(the_Y,0,1). The first range survived the source book being closed, but the second did not. I went to Links on the Edit menu, and tried to update the links, and the error message told all: Microsoft Excel cannot find 'the_Y2' on 'source.xls'. There are two possible reasons: • The name you specified may not be defined. • The name you specified is defined as something other than a rectangular cell reference. Check the name and try again. The second bullet tells you just what Excel is able to read from a closed file: a rectangular cell reference. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ tbieri wrote: Hi, I have a Summary workbook that has multiple charts that are copied/linked to different source workbooks. The Source workbook chart is created using dyamic named ranges (e.g. source_xvalues). I am getting odd behavior and don't understand how or what order excel looks at different workbooks. If both the Summary and Source workbooks are open, life is grand. In some cases, if I close the Source, the series disappear from the Summary chart. In other cases, the series don't disappear. Any suggestions as to how to handle this problem? Regards Tim |
#3
|
|||
|
|||
Named Ranges in Seperate Workbook
Jon,
Thanks for the information. I think I found the source of my error, the name references were messed up, pointing to the Summary instead of the Source. For example, the named range in the Source would be Xvalues and defined as 'SheetName'!$A$1:$A$10. I also used Yvalues1,2 and 3, which were offset from Xvalues. When I looked in the Summary workbook, there would be defined names, which I did not define, probably created when the graph was copy/pasted. The defined names pointed back to the Source workbook. Close the Source and bye-bye pretty graph lines. I think it is fixed for the moment by changing the series formula to refer to the Source workbook named range (using your utilit/marcro) instead of the Summary named ranges, and deleting the named ranges in the Summary workbook. For example - Series(,'Source'!Xvalues,'Source'!Yvalues,1). I have graphs with multiple series using this method. I still do not update links when I open the Summary book, prefering to do it manually. Not sure how clear this came across, please respond if there are questions. Regards, Tim PS thanks for the great website -----Original Message----- Hi, I have a Summary workbook that has multiple charts that are copied/linked to different source workbooks. The Source workbook chart is created using dyamic named ranges (e.g. source_xvalues). I am getting odd behavior and don't understand how or what order excel looks at different workbooks. If both the Summary and Source workbooks are open, life is grand. In some cases, if I close the Source, the series disappear from the Summary chart. In other cases, the series don't disappear. Any suggestions as to how to handle this problem? Regards Tim . |
Thread Tools | |
Display Modes | |
|
|