Named Range Brings "error" When Using it in a Chart

I am not familiar enough to say. Please see the attachment I was finally able to upload this morning. I am using a CHOOSE function (or trying to) to get it to choose between one of three different data sets (on the Chart Data Source sheet). There are 3 sets of 3 rows of data, and each have a number on the far left that changes based on what is selected under the drop-down menus on the first sheet, and those numbers (ranging from 1 to 3) should tell the data in the chart which row of data to pull from.

You can see each of these formulas named under the names menu, and they are USEDATAG1A, USEDATAG1B, and USEDATAG2.

I hope that answers your question. I believe that makes this dynamic, but having my little experience with charts at this point, I could be wrong. Please let me know if that helps or not. It would be amazing if I could get this to work.

Much appreciated, Colby

I hope my post made some sense. Maybe not?

Hey ProtonLeah,

Wow, you did a ton of work here. Thanks for doing that. I did run into an issue though. The "trend analysis 2" chart ran into an error. It was referencing the "Overall Customer Stats 2" sheet, and something weird was happening on that sheet. It ended up with errors, and without that working properly, you can't have a separate statistic for that chart to compare with the other one.

I think something in between the two would be perfect. The direction I was going with the workbook I uploaded on this thread was just to match the suggestion by Andy Pope (another Guru on here), and a workbook he linked me to when I was having trouble making the y-axis data change formats (from currency to percentages to whole numbers). He told me to use named ranges to do it, and there was a workbook on the thread he linked me to, which he previously uploaded >> https://www.excelforum.com/excel-cha...er-format.html

The named range I was trying to use was pretty much identical to his, and his worked. The only difference is his was all contained on the same worksheet, and mine was referencing multiple sheets. I don't know if that's what the problem was, but it's the only thing I could think of.

I will have to look through all the changes you made so I can understand it, in case I need to update it later. At the moment it's a little daunting. You truly are a 'guru'!

Having said that, I still need to understand things, and be able to tweak them. Let me know if you have any suggestions. I appreciate your help either way.

- Colby

Originally Posted by MarvinP

Hi Nitro,

I'm not sure I understand the problem. I'm pretty sure the Choose() function isn't what you want. I'd expect an indirect of a cell that would be the name of the range is what you might want. I don't see that in your example. I'm trying to create a simpler example and failing. I'll toss this up to the smart gurus and see if any of them can get you closer to an answer.

Hi MarvinP,

I am using the Choose function to basically copy the method that another Guru (Andy Pope) used in a spreadsheet he linked me to, when I asked for help with my charts. I was struggling to make the y-axis data update the format when the source data changed (from currency to whole numbers to percentages). I need the y-axis to change with the data. The problem he pointed out is that I was using conditional formatting with my source data, and the format will not follow the conditional formatting. What he suggested is having named ranges, and make the source data shift to a different set of cells with the correct formatting whenever I needed the format to change.

He linked me to this thread, where he posted an excel sheet demonstrating this... https://www.excelforum.com/excel-cha...er-format.html

He used the choose function to switch between 2 different lists of data, one was a percentage and the other whole numbers. I just copied that.

The only difference I see is that all of his data, the named ranges, formulas, and chart were on the same worksheet, whereas mine is on multiple sheets. I don't have enough familiarity with charts and named ranges to figure out the problem.

I am trying to have the data set for "Customer 1" to be the named range/formula "USEDATAG1A" and the data set for "Customer 2" to be "USEDATAG1B". Those formulas/ranges are correct. They bring up the correct data, and update automatically when things are changed. So they should work. But whenever I try to use those named ranges for the data, it tells me there's some error with the formula. I am really confused where that issue is.