Refer to named range in another file

**Bob@Sun** · 05-04-2010, 05:36 AM

Hello,

I am trying to build something like a data base and then calculate result in a separate workbook.
Having one file with the data for each month I am assigning named ranges for Column A - "Name" and Column B - "Items".
I another file I need to use the Sumproduct function to count items for each name.
Is it possible to make the reference dynamic so my Master file to refer to the named ranges of the open file?
Example: If I open the file for Jan the Master file to refer to named range "Name" and "Items" of this file, If open the file for March to refer to the named ranges of this file.

Thanks!

**JBeaucaire** · 05-04-2010, 08:51 AM

Hi Bob. As long as you're going to be opening a specific file each time, you could put the name of that workbook in your main workbook in a cell, then use that cell to construct an INDIRECT() reference inside your SUMPRODUCT() formulas, or COUNTIF() which sounds like it might be a better choice.

If A1 held the workbook name you want to use at any given moment, in the same workbook create a named range NAMES with an indirect() reference that refers to:
=INDIRECT("[" & 'Main Workbook'!$A$1 & "]Sheet1!$A$1:$A$100")

Then you should able to use a simple COUNTIF(Names, "Name1") to count all the times "Name1" appears in the range defined.

**Bob@Sun** · 05-04-2010, 10:03 AM

I can't make it to work

**Bob@Sun** · 05-04-2010, 10:40 AM

=OFFSET('Sheet - 1'!$E$2,0,0,COUNTA('Sheet - 1'!$E$2:$E$65536),1)

That is the range I need to define in the data base files.
How do I incorporate it with the Indirect function?

**JBeaucaire** · 05-04-2010, 10:53 AM

Why does it need to be dynamic? A lot of plumbing. I only use dynamic formulas to keep data validation lists tidy, ranges for countif() formulas don't need that.

**Bob@Sun** · 05-04-2010, 11:08 AM

Well that is what I need. The Countif function was only for an example. My file holds much more complicated formulas.

**Bob@Sun** · 05-04-2010, 01:22 PM

Any ideas?

Refer to named range in another file

LinkBack

Thread Tools

Rate This Thread

Display

Refer to named range in another file

Re: Refer to named range in another file

Re: Refer to named range in another file

Re: Refer to named range in another file

Re: Refer to named range in another file

Re: Refer to named range in another file

Re: Refer to named range in another file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions