Using the Indirect.Ext function
 

I keep trying to use this function and it gives me an error even if the
source workbook is open or closed. I have a reference in a cell that
pulls the cell value in another workbook based on the intersection of
two named ranges. The reference looks like this:

='K:\ACCTG\25 BUDGET\Department Budgets\GAMES 2005.xls'!Jan
'K:\ACCTG\25 BUDGET\Department Budgets\GAMES 2005.xls'!PR

Where "Jan" is the named column and "PR" is the named row.

I want the named column to change based on a cell value so I wrote the
following formula. The cell that holds the value is c179. The formula
looks like this:

=INDIRECT.EXT("'K:\ACCTG\25 BUDGET\Department Budgets\GAMES 2005.xls'!"
& C179 & " 'K:\ACCTG\25 BUDGET\Department Budgets\GAMES 2005.xls'!Rev")

This just gives me a REF#! error. Is what I'm trying to do not
possible or am I just wrong in how I am writing this?

Any help would be greatly appreciated.

Henry


--
henryhbb
------------------------------------------------------------------------
henryhbb's Profile: http://www.excelforum.com/member.php...o&userid=15774
View this thread: http://www.excelforum.com/showthread...hreadid=272888

Hi
the original formula (without INDIRECT.EXT) does not seem to be a valid
reference either.

Also you can't access defined names from closed workbooks. This is not
possible!


--
Regards
Frank Kabel
Frankfurt, Germany


henryhbb wrote:
I keep trying to use this function and it gives me an error even if
the source workbook is open or closed. I have a reference in a cell
that pulls the cell value in another workbook based on the
intersection of two named ranges. The reference looks like this:

='K:\ACCTG\25 BUDGET\Department Budgets\GAMES 2005.xls'!Jan
'K:\ACCTG\25 BUDGET\Department Budgets\GAMES 2005.xls'!PR

Where "Jan" is the named column and "PR" is the named row.

I want the named column to change based on a cell value so I wrote
the
following formula. The cell that holds the value is c179. The
formula looks like this:

=INDIRECT.EXT("'K:\ACCTG\25 BUDGET\Department Budgets\GAMES
2005.xls'!" & C179 & " 'K:\ACCTG\25 BUDGET\Department Budgets\GAMES
2005.xls'!Rev")

This just gives me a REF#! error. Is what I'm trying to do not
possible or am I just wrong in how I am writing this?

Any help would be greatly appreciated.

Henry