|
Dynamic Charts between Files
Hi,
I've been working with dynamic named ranges, charts and pivot tables. They seem to work fine, as long as the source and chart are in the same file. When they are in different files, I'm getting "Your formula contains invalid external reference to a worksheet". The data is horizontal, a la "Jan Feb Mar" with me adding new columns to the right and capturing the last 12 mths. When I have the ranges defined in the source file, I can click in the Refers to field and it puts the marquee around the correct range. I can also hit <F5 and put the range name in there and the correct range gets selected. However, when I click the series in the chart and try to put the range names into the series formula, I get the error above. The original series formula is =SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$8:$AT$8,1) The named ranges look like: Name: Months_RecCallOverallLabels: Refers to: =OFFSET(FIN_Months!$AA$7,0,COUNTA(FIN_Months!$AB$7 :$IV$7)-11,1,12) Name: Months_RecCallOverallBot: Refers to: =OFFSET(FIN_Months!$AA$7,1,COUNTA(FIN_Months!$AB$7 :$IV$7)-11,1,12) With the range names, it looks like: =SERIES(Months_RecCallOverallLabels,Months_RecCall OverallLabels,Months_RecCallOverallBot,1) I've tried it with and without replacing the name_ref argument. I've tried it without the file references (although it escapes me how they would find each other without that). I've even tried defining the named ranged in the target file, hoping the file name would allow that to work. Argh. What am I missing? Thanks! Neva |
Dynamic Charts between Files
You're missing the parent of the name. The name needs to be referenced with
a sheet or workbook name. Something like this: =SERIES('[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallBot,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message oups.com... Hi, I've been working with dynamic named ranges, charts and pivot tables. They seem to work fine, as long as the source and chart are in the same file. When they are in different files, I'm getting "Your formula contains invalid external reference to a worksheet". The data is horizontal, a la "Jan Feb Mar" with me adding new columns to the right and capturing the last 12 mths. When I have the ranges defined in the source file, I can click in the Refers to field and it puts the marquee around the correct range. I can also hit <F5 and put the range name in there and the correct range gets selected. However, when I click the series in the chart and try to put the range names into the series formula, I get the error above. The original series formula is =SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$8:$AT$8,1) The named ranges look like: Name: Months_RecCallOverallLabels: Refers to: =OFFSET(FIN_Months!$AA$7,0,COUNTA(FIN_Months!$AB$7 :$IV$7)-11,1,12) Name: Months_RecCallOverallBot: Refers to: =OFFSET(FIN_Months!$AA$7,1,COUNTA(FIN_Months!$AB$7 :$IV$7)-11,1,12) With the range names, it looks like: =SERIES(Months_RecCallOverallLabels,Months_RecCall OverallLabels,Months_RecCallOverallBot,1) I've tried it with and without replacing the name_ref argument. I've tried it without the file references (although it escapes me how they would find each other without that). I've even tried defining the named ranged in the target file, hoping the file name would allow that to work. Argh. What am I missing? Thanks! Neva |
Dynamic Charts between Files
OK, Jon,
I hope you don't mind a few details, because I know I'm just missing something minor. I've got the range names created in the file containing the charts, "ch_RecentCall(Fin).xls". The ranges "Refers To" look like: =OFFSET('[05-CSAT-Tracker.xls]FIN_Months'!$AA$7,0,COUNTA('[05-CSAT-Tracker.xls]FIN_Months'!$AB$7:$IV$7)-11,1,12) When I try to put that in the chart series formula, I get the "invalid external reference to a worksheet" message. The series formula looks like this when it fails: =SERIES(Months_RecCallOverallLabels,Months_RecCall OverallLabels,Months_RecCallOverallBot,1) I also tried it without the range name in the name_ref argument. Neva Jon Peltier wrote: You're missing the parent of the name. The name needs to be referenced with a sheet or workbook name. Something like this: =SERIES('[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallBot,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message oups.com... Hi, I've been working with dynamic named ranges, charts and pivot tables. They seem to work fine, as long as the source and chart are in the same file. When they are in different files, I'm getting "Your formula contains invalid external reference to a worksheet". The data is horizontal, a la "Jan Feb Mar" with me adding new columns to the right and capturing the last 12 mths. When I have the ranges defined in the source file, I can click in the Refers to field and it puts the marquee around the correct range. I can also hit <F5 and put the range name in there and the correct range gets selected. However, when I click the series in the chart and try to put the range names into the series formula, I get the error above. The original series formula is =SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$8:$AT$8,1) The named ranges look like: Name: Months_RecCallOverallLabels: Refers to: =OFFSET(FIN_Months!$AA$7,0,COUNTA(FIN_Months!$AB$7 :$IV$7)-11,1,12) Name: Months_RecCallOverallBot: Refers to: =OFFSET(FIN_Months!$AA$7,1,COUNTA(FIN_Months!$AB$7 :$IV$7)-11,1,12) With the range names, it looks like: =SERIES(Months_RecCallOverallLabels,Months_RecCall OverallLabels,Months_RecCallOverallBot,1) I've tried it with and without replacing the name_ref argument. I've tried it without the file references (although it escapes me how they would find each other without that). I've even tried defining the named ranged in the target file, hoping the file name would allow that to work. Argh. What am I missing? Thanks! Neva |
| All times are GMT +1. The time now is 02:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com