INDIRECT EXTERNAL DATA

[Guest]

I have the following statement in a parameter going against sql server
=indirect("Domestic!A"&row()) it works fine when I first put it in and
retrieve data.
The problem is when I save the spread sheet and then later open it back up.
The following message then occurs
"Coluld not obtain parameter from reference (Bad name or workbook not open).
Proceed to prompt for value?"

Is there another function that I need to use in a parm external situation
other than indirect? Thank YOu

 

[Ron Rosenfeld]

I have the following statement in a parameter going against sql server
=indirect("Domestic!A"&row()) it works fine when I first put it in and
retrieve data.
The problem is when I save the spread sheet and then later open it back up.
The following message then occurs
"Coluld not obtain parameter from reference (Bad name or workbook not open).
Proceed to prompt for value?"

Is there another function that I need to use in a parm external situation
other than indirect? Thank YOu

You could use Longre's INDIRECT.EXT function, available in his free
morefunc.xll available at http://xcell05.free.fr

Unlike the INDIRECT built-in function, INDIRECT.EXT can also return the value
of a cell whose workbook is closed.


--ron

 

[Guest]

Ron i've tried that it doesn't seem to work.
=indirect.ext("Domestic!A"&row()) is this syntax correct?
Thanks

 

[Ron Rosenfeld]

Ron i've tried that it doesn't seem to work.
=indirect.ext("Domestic!A"&row()) is this syntax correct?
Thanks

What does "doesn't seem to work" mean?
--ron

 

[Guest]

Bad parm type excel is expecting a different kind of value than provided

Thanks

 

[Ron Rosenfeld]

Bad parm type excel is expecting a different kind of value than provided

Thanks

I cannot find the error message with that wording. And a formula using the
same syntax as yours on my system does not result in any error. I also pasted
your error message into the MSKB search engine and it came up empty. Perhaps
some other program is generating that wording.

With the formula in Sheet2!A4, using the same syntax as you show:

=INDIRECT.EXT("Sheet1!A"&ROW())

it returns the contents of Sheet1!A4 as expected.

If you want to use this command to refer to an external workbook, which is what
I thought you required, the various acceptable syntaxes are outlined in HELP
for the INDIRECT.EXT function.

=========================================

The Reference argument can be one of the following :

- Another range in the same workbook :

If the range is in the same workbook, the Reference argument should contain its
address in the usual form, like "A1", "Sheet1!A1", "'Sheet 1'!A1" and so on.

- A range in another workbook (absolute path) :

The pattern of the Reference argument is :
"'Drive:\Folder\[WorkbookName.xls]SheetName'!A1". Don't forget the quotes !

- A range in another workbook (relative path) :

If the workbook is in the same directory : "'[WorkbookName.xls]SheetName'!A1".

If it is in another directory, for instance the parent directory :
"'..\[WorkbookName.xls]SheetName'!A1".

- A workbook-level name :

If RangeName is a workbook-level name (not sheet-level) :
"'Path\WorkbookName.xls'!RangeName (don't put the name of the workbook in []).

==========================================


--ron

 

[Guest]

your right I can get this to work when in a cell. However, when it is in a
parm in a
sql query it gets the error. Thanks for you help I'll post when I find an
answer.

Bad parm type excel is expecting a different kind of value than provided

Thanks

I cannot find the error message with that wording. And a formula using the
same syntax as yours on my system does not result in any error. I also pasted
your error message into the MSKB search engine and it came up empty. Perhaps
some other program is generating that wording.

With the formula in Sheet2!A4, using the same syntax as you show:

=INDIRECT.EXT("Sheet1!A"&ROW())

it returns the contents of Sheet1!A4 as expected.

If you want to use this command to refer to an external workbook, which is what
I thought you required, the various acceptable syntaxes are outlined in HELP
for the INDIRECT.EXT function.

=========================================

The Reference argument can be one of the following :

- Another range in the same workbook :

If the range is in the same workbook, the Reference argument should contain its
address in the usual form, like "A1", "Sheet1!A1", "'Sheet 1'!A1" and so on.

- A range in another workbook (absolute path) :

The pattern of the Reference argument is :
"'Drive:\Folder\[WorkbookName.xls]SheetName'!A1". Don't forget the quotes !

- A range in another workbook (relative path) :

If the workbook is in the same directory : "'[WorkbookName.xls]SheetName'!A1".

If it is in another directory, for instance the parent directory :
"'..\[WorkbookName.xls]SheetName'!A1".

- A workbook-level name :

If RangeName is a workbook-level name (not sheet-level) :
"'Path\WorkbookName.xls'!RangeName (don't put the name of the workbook in []).

==========================================


--ron

 

[Ron Rosenfeld]

your right I can get this to work when in a cell. However, when it is in a
parm in a
sql query it gets the error. Thanks for you help I'll post when I find an
answer.

I have no familiarity at all with SQL queries, so can't be of much help there.

Sorry.


--ron