Help with Named Ranges

**mlk** · 03-05-2010, 08:57 AM

Can named ranges only be used on data within the same workbook?

I have a data file that is used as source data for numerous pivot reports - the pivot reports are different files.

I applied a dynamic range name to the source data but find I can't use the range name for external pivot tables if the source file is closed. When I have the pivot tables and the source file open, the pivots work - however as soon as I close the source I get a message that the source can't be found. If I open the source again, it works, but stops working once closed.

I don't want to open the source everytime I need to update all the reports.

Is there something else I need to do - or is there something else I can do?

Thanks for any assistance you can provide.

**sg2010** · 03-05-2010, 09:03 AM

Not sure if this is what you are looking for. But I found someone else with a similar problem who had their's solved.

http://www.excelforum.com/excel-prog...-workbook.html

**NBVC** · 03-05-2010, 09:04 AM

Can't say for sure that it works, never tried it, but you can try the PULL fuction code..

See link in this article describing referencing closed workbooks.

http://www.dailydoseofexcel.com/arch...sed-workbooks/

**mlk** · 03-05-2010, 09:18 AM

Thank you both for you quick responses.

I was hoping it was something easy I was just missing - but it appears to not be a simple fix. I will give your suggestions a try and see if I can make it work.

Much appreciated!

**NBVC** · 03-05-2010, 09:23 AM

If you explain further, perhaps with examples (even a spreadsheet example) how you are referencing the named range in the other workbook, maybe someone can figure out another way... no guarantee... but at least we will know for sure after seeing your actual dilemma.

**mlk** · 03-05-2010, 10:32 AM

What I'm doing is fairly basic.

In workbook 1, I applied a dynamic named range to the data (range name = "BaseData") that is the dat source used in pivot reports - so that when rows and columns are added, I don't have to update the pivot table ranges.

In workbook 2, I create a pivot table by opening workbook 1 and selecting the data to be used as the range for the pivot (as per normal) - but then I replace the cells that follow the file name with the named range "BaseData".

It works just fine as long as I keep both the source file and the pivot report open, but when closing the source - the report can no longer find the source.

Hopefully that helps clarify what I'm trying to do.