Name Ranges...

[Vixter]

I have one workbook where I have named ranges.
Is it possible to somehow use those names in formulas in a completely
different workbook? Excel 2003.
Any help is appreciated.

 

[Vixter]

P.S. It's not just using the same name. I need to use the same ranges as the
two workbooks are connected.

 

[Jacob Skaria]

The below would refer to the first cell of named range Name1 from saved
workbook Mybook.xls

=INDEX(MyBook.xls!Name1,1)

 

[Vixter]

Hi Jacob, thanks but I can't seem to work it.
I basically need a sumproduct of "Amount" from the other workbook. (I'm
doing it between dates). In my workbook with the names I use the following
formula:
=SUMPRODUCT((Date_Jan>=B15)*(Date_Jan<=B16),Amount_Jan)
I need to use the same formula but without just copying it from one workbook
to another.
Is this possible?
Thanks.

 

[Jacob Skaria]

You need to mention the workbook name. Names collection represents all the
names in the active workbook. (names defined with the Workbookname and
"WorksheetName!" as prefix).

 

[Vixter]

I'm feeling thicker than two bricks. Can you put it simply?
The workbook that contains the names is called "Pillars" and the sheet the
data is in is called "Jan". Can you use the following formula to stick those
in?
=SUMPRODUCT((Date_Jan>=B15)*(Date_Jan<=B16),Amount_Jan)
Sorry - I am still learning excel.
Let me know if I need to explain better?

 

[Jacob Skaria]

Try

'if open
=SUMPRODUCT((Pillars.xls!date_Jan>=B15)*
(Pillars.xls!date_Jan<=B16),Amount_Jan)

'if Pillars.xls is closed
=SUMPRODUCT(('D:\Pillars.xls'!date_Jan>=B15)*
('D:\Pillars.xls'!date_Jan<=B16),Amount_Jan)

PS: Make sure Amount_Jan is of the same dimension as the named range Date_Jan

 

[Vixter]

Thanks for all your input and time. Nothing is working - and because I'm on a
network the location of the file is long.
What I've done is calculate the formula without the names (just as a range -
while Pillars.xls was open) and then I simply typed in the range names over
the cell ranges - seemed to have worked.
I'm still learning.
Thanks again.

 

[Jacob Skaria]

Try whether the below is returning the first item from the named range

'with file open
=INDEX(Pillars.xls!date_Jan,1)

 

[Vixter]

It's returning a number item but not the right amount.
Don't worry. Even though I'm using a long-winded formula it should be ok.
Thanks anyway.
Vixter

 

[Archimedes' Lever]

You can also assign a drive letter to a network directory and all your
users access that network directory through that drive letter assignment.

An alias.