Thread: Linking to Named Range

Hello,

I've setup named ranges and am currently linking to them as the data ranges for my charts (this allows me to setup dynamic charts that auto-update based on newly added data). The actual process works fine, but I've been having a problem that is driving me crazy:

When I choose the source data, then "Series Values", I need to type something like this:
"=SheetName!Named_Range_Name"
OR "=WorkbookName.xls!Named_Range_Name"

Everything works fine, but when I save the file, close, and reopen, all of the references break and change to this:
"=[0]!Named_Range_Name", and they don't work anymore.

Very aggravating as I have a lot of charts. Is there some code I can type in instead of Sheetname or Workbookname that says "look in this workbook, whatever its name may be"?

I'm also using a binary excel file (.xlsb), if that makes a difference. If anyone can help me with this I'm trying to fix it soon so help would be much appreciated!

Thanks,
Colin

Thanks for the response. Here are some examples of the names I've used, each name has 3 parts based on the sheet name, column(s), and vertical range:

"MonthlySummary_ColA_Last7"
"DailySummary_ColT_Last30"
"WeeklySummary_ColP_ToStart"

Does that answer your question?

Thanks for the response. All of the references break, so I'll give a random example of one of them:

Range: "MonthlySummary_ColA_Last6"

Range Formula: "=OFFSET(INDIRECT("'Monthly Summary'!A"&MATCH(LOOKUP(Dashboard!$B$1,'Monthly Summary'!$AA:$AA),'Monthly Summary'!$AA:$AA)),-5,0,6)"

(formulas for Column A are kinda ugly, but the other ones, such as MonthlySummary_ColB_Last6, are just offsets of ColA and are much cleaner)

In the chart source data, I'll choose the data range (regardless of which range or series I'm looking at, I'll have the same result).
I'll type either:
=Billing_Metrics.xlsb!MonthlySummary_ColA_Last6
-OR-
=Dashboard!MonthlySummary_ColA_Last6
-OR-
='Monthly Summary'!MonthlySummary_ColA_Last6

Where "Dashboard" and "Monthly Summary" represent sheet names. Regardless of which of the 3 I type, excel changes it (when I go back to look at it again) to:
=Billing_Metrics.xlsb!MonthlySummary_ColA_Last6

This seems to all work fine until I close the file and open it up again, excel changes all of the chart references from:
=Billing_Metrics.xlsb!MonthlySummary_ColA_Last6
-TO-
=[0]!MonthlySummary_ColA_Last6

Whatever this "[0]" is, it doesn't work so I have to manually change back all the references to the 3 listed above.

I've found that I don't have this problem when the file is in .xls format (vs. .xlsb), so if this is a huge problem to solve then no worries...

To give you a little context, I'm using .xlsb format because I'm creating this workbook on Excel '07. I started with .xlsx format which worked fine, and created a small file size, but I need to share this workbook with other individuals who use Excel '03 (and I don't want to make them download the patch to view '07 files). So I tried saving the file as .xls (for '97-'03 workbooks) which works fine except the file got huge (was 0.7 megs before conversion, now it's at 5.7 megs) and also laggy. I next noticed that I can save as binary files which shrinks it back down, but I've also recently seen that binary notebooks open slowly with '03 (there's some type of initial conversion loading bar that runs prior to opening the file with '03). If you guys have any suggestions that solve for this problem, I may be able to bypass the original problem.

Sorry for the long post, but I wanted to be thorough.

Take care,
Colin

Small Observation:

I have macros in my file so I need to use a macro-enabled workbook. When I try using the file format .xlsm, the same thing happens. So far the only format which seems to work is .xls (for Excel '97-'03).

Colin