Apparent Defined Name Problem

**Huhenyo** · 02-10-2012, 05:04 AM

I have a workbook where I'm using quite a few defined names. I'm using the defined names in the =Series(name,x,y,position) of a bunch of graphs to be able to create a horizontal "goal" line from a single cell value. It works beautifully in all but two cases, and I can't for the life of me figure out what the reason for the two not working. I've tried redefining the names, I've tried assigning different values to the defined names, and a bunch of other diagnostic trials that have all failed to give me a clue as to what the problem is. I would be very appreciative is someone out there could figure this out for me. I've attached the file so you can see what I'm talking about.

Specifically the two defined names that aren't working are "Colorectal_Screen_Goal" and "Comp_CVD_Goal." All of the other defined names work flawlessly. All of the names were defined on the "Individual Scoreboard" tab. The data series that won't accept the defined names (for the series of the goal) are on the Cancer tab the Colorectal Cancer Screen 51-80 and on the CVD-Other-STI tab the IHD: Comp CVD Assessment goal (both are the purple series. As you can see, I currently have a user defined array entered because my defined names won't work like on all the other graphs.

If anyone can figure this out I will be super happy and grateful..

Thank you so much!

**teylyn** · 02-10-2012, 06:42 AM

Hello,

I had no trouble entering the range names in the series definition. You do know that you need to use the sheet name and the range name when you enter a range name in a chart series, right? So, for example, I entered

='Individual Scorecard'!Colorectal_Screen_Goal

and then Excel will turn that into

='GPRA Samples.xlsx'!Colorectal_Screen_Goal

when the dialog is closed. See attached.

I did notice some rendering issues, i.e. after saving the file, some charts disappeared and would only become visible again after scrolling down and up again. Maybe the file is becoming corrupt. You may want to back it up and then re-create it from scratch. Copy only the data, not the sheets, into a new file and re-create the charts. It's a lot of work, but something's funny with that workbook. Better spend an hour now than lose everything.

cheers,

**Huhenyo** · 02-11-2012, 01:12 AM

Thanks for the help, teylyn. Since it seems that the workbook works on your computer, could you also fix the Colorectal graph as well. I noticed that you fixed the CVD chart. I've tried remaking the whole thing cutting and pasting into a new spreadsheet, and it still won't work. I have no idea what is wrong, but since yours seems to be working, could you fix that for me? I would really appreciate it.

Thanks again.

I hope to visit New Zealand some day. I hear it is beautiful.

**teylyn** · 02-11-2012, 04:49 AM

I swear I changed the Colorectal chart too before I attached the file. Well, maybe not.

Funny thing is that when I save, all the charts disappear and only display again when I scroll down a few screens and back up again.

I don't trust this file. It is corrupt.

Anyway, attached is the version with the Colorectal chart goal applied with the range name.

cheers,

PS: Visiting NZ and living in NZ are two very different perspectives. Yes, it is beautiful, but as a resident, I get to see beyond the glossy tourist brochures. It's beautiful and green because there are not enough people to destroy it. It's the most aggressively farmed country in the world, with all the drawbacks. Just, some areas have simply not be turned into farmland, yet, so they are still beautiful.