|
Dynamic range in chart
Hi,
I have created a named range called Net_CLI which has the following formula =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") Presently I expect the vlookup part of the above formula to return H, so in all I expect the named range to give me ---- Trends!$C$4:$H$4 If I use the above named range in Data Validation, then I get the relevant values in drop down. But If I have a chart (in the same worksheet) in which for the first series in place of existing entry in the values textbox as =Trends!$C$4:$H$4 , I enter as Net_CLI , then I get the following message:- "The formula you typed contains an error. Try one of the following: - Make sure you've included all parantheses and required arguments. - To get assistance with a function.... - ... - ... -..." What is the mistake am doing above. regards, HP India |
Dynamic range in chart
Hi,
The problem is that chart series can not directly use the result of an INDIRECT() formula. One way around this is to reference the named range via another named range. Named range CHTA builds an indirect reference to the charts labels. The labels start in A2 on sheet1 and the number of labels in controlled by the end cell reference which is stored in E3. So for our example Cell E3: =A10 Named range CHTA: =INDIRECT("Sheet1!A2:"&Sheet1!$E$3) Add a named range that the chart can use plus one of the data in the next column. CHTLABELS: =Choose(1,CHTA) CHTDATA: =OFFSET(CHTLABELS,0,1) Now the chart series formula will look like this. =SERIES(,Book1!CHTLABELS,Book1!CHTDATA,1) Cheers Andy Hari wrote: Hi, I have created a named range called Net_CLI which has the following formula =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") Presently I expect the vlookup part of the above formula to return H, so in all I expect the named range to give me ---- Trends!$C$4:$H$4 If I use the above named range in Data Validation, then I get the relevant values in drop down. But If I have a chart (in the same worksheet) in which for the first series in place of existing entry in the values textbox as =Trends!$C$4:$H$4 , I enter as Net_CLI , then I get the following message:- "The formula you typed contains an error. Try one of the following: - Make sure you've included all parantheses and required arguments. - To get assistance with a function.... - ... - ... -..." What is the mistake am doing above. regards, HP India -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Dynamic range in chart
Andy,
Thanks for the post. Indirect not being directly usable in Chart series is new to me. Im not sure, but somehow, am still not able to make my formula work. I have :- Net_CLI =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") and I created a new named range NetCLI = Choose (1, Net_CLI) Both of the above named ranges work correctly when I use them in data validaton (or as an argument within Sum function). But If I use NetCLI within Values textbox of my chart series (Source data dialog box) then I get the error which I mentioned before. Please guide me. regards, HP India Andy Pope wrote: Hi, The problem is that chart series can not directly use the result of an INDIRECT() formula. One way around this is to reference the named range via another named range. Named range CHTA builds an indirect reference to the charts labels. The labels start in A2 on sheet1 and the number of labels in controlled by the end cell reference which is stored in E3. So for our example Cell E3: =A10 Named range CHTA: =INDIRECT("Sheet1!A2:"&Sheet1!$E$3) Add a named range that the chart can use plus one of the data in the next column. CHTLABELS: =Choose(1,CHTA) CHTDATA: =OFFSET(CHTLABELS,0,1) Now the chart series formula will look like this. =SERIES(,Book1!CHTLABELS,Book1!CHTDATA,1) Cheers Andy Hari wrote: Hi, I have created a named range called Net_CLI which has the following formula =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") Presently I expect the vlookup part of the above formula to return H, so in all I expect the named range to give me ---- Trends!$C$4:$H$4 If I use the above named range in Data Validation, then I get the relevant values in drop down. But If I have a chart (in the same worksheet) in which for the first series in place of existing entry in the values textbox as =Trends!$C$4:$H$4 , I enter as Net_CLI , then I get the following message:- "The formula you typed contains an error. Try one of the following: - Make sure you've included all parantheses and required arguments. - To get assistance with a function.... - ... - ... -..." What is the mistake am doing above. regards, HP India -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Dynamic range in chart
Hari wrote: Andy, Thanks for the post. Indirect not being directly usable in Chart series is new to me. Im not sure, but somehow, am still not able to make my formula work. I have :- Net_CLI =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") and I created a new named range NetCLI = Choose (1, Net_CLI) Both of the above named ranges work correctly when I use them in data validaton (or as an argument within Sum function). But If I use NetCLI within Values textbox of my chart series (Source data dialog box) then I get the error which I mentioned before. Could somebody please outline as to what am doing wrong. Regards, HP India |
Dynamic range in chart
Hari wrote: Hari wrote: Andy, Thanks for the post. Indirect not being directly usable in Chart series is new to me. Im not sure, but somehow, am still not able to make my formula work. I have :- Net_CLI =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") and I created a new named range NetCLI = Choose (1, Net_CLI) Both of the above named ranges work correctly when I use them in data validaton (or as an argument within Sum function). But If I use NetCLI within Values textbox of my chart series (Source data dialog box) then I get the error which I mentioned before. When I try to edit in the formula bar directly by changing =SERIES(Trends!$B$4,Trends!$C$3:$H$3,Trends!$C$4:$ H$4,1) to =SERIES(Trends!$B$4,Trends!$C$3:$H$3,NetCLI,1) I get a different error which is, A formula in this worksheet contains one or more invalid references Verify that your formulas contain a valid path, workbook, range name, and cell reference. Regards, HP India |
Dynamic range in chart
You need to include a workbook or worksheet reference when using a named
range. =SERIES(,Sheet1!$B$4:$B$7,Book1.xls!Net_CLI,1) Cheers Andy Hari wrote: Hari wrote: Hari wrote: Andy, Thanks for the post. Indirect not being directly usable in Chart series is new to me. Im not sure, but somehow, am still not able to make my formula work. I have :- Net_CLI =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") and I created a new named range NetCLI = Choose (1, Net_CLI) Both of the above named ranges work correctly when I use them in data validaton (or as an argument within Sum function). But If I use NetCLI within Values textbox of my chart series (Source data dialog box) then I get the error which I mentioned before. When I try to edit in the formula bar directly by changing =SERIES(Trends!$B$4,Trends!$C$3:$H$3,Trends!$C$4:$ H$4,1) to =SERIES(Trends!$B$4,Trends!$C$3:$H$3,NetCLI,1) I get a different error which is, A formula in this worksheet contains one or more invalid references Verify that your formulas contain a valid path, workbook, range name, and cell reference. Regards, HP India -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Dynamic range in chart
Andy Pope wrote: You need to include a workbook or worksheet reference when using a named range. =SERIES(,Sheet1!$B$4:$B$7,Book1.xls!Net_CLI,1) Cheers Andy It works GREAT. TONS of thanks to you. Does it mean that anytime I use a Named range, I would have to affix the worksheet/workbook name as well in charts. Its strange because in my case, when I was calculating the exact address of the named range, I had included worksheet names in that. More so because I was using the named range in the same workbook only regards, HP India Hari wrote Hari wrote: Hari wrote: Andy, Thanks for the post. Indirect not being directly usable in Chart series is new to me. Im not sure, but somehow, am still not able to make my formula work. I have :- Net_CLI =INDIRECT("Trends!$C$4:$" & VLOOKUP(COUNTA(Trends!$B$4:$V$4),'Legend Sheet'!$B$31:$C$50,2,FALSE) & "$4") and I created a new named range NetCLI = Choose (1, Net_CLI) Both of the above named ranges work correctly when I use them in data validaton (or as an argument within Sum function). But If I use NetCLI within Values textbox of my chart series (Source data dialog box) then I get the error which I mentioned before. When I try to edit in the formula bar directly by changing =SERIES(Trends!$B$4,Trends!$C$3:$H$3,Trends!$C$4:$ H$4,1) to =SERIES(Trends!$B$4,Trends!$C$3:$H$3,NetCLI,1) I get a different error which is, A formula in this worksheet contains one or more invalid references Verify that your formulas contain a valid path, workbook, range name, and cell reference. Regards, HP India -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| All times are GMT +1. The time now is 07:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com