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

On Fri, 3 Feb 2006 07:57:24 -0800, saveas getting rid of formulas
. com wrote:

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

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

On Fri, 3 Feb 2006 07:57:24 -0800, saveas getting rid of formulas
. com wrote:

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

On Fri, 3 Feb 2006 09:11:54 -0800, saveas getting rid of formulas
. com wrote:

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

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

Thanks

"Ron Rosenfeld" wrote:

On Fri, 3 Feb 2006 09:11:54 -0800, saveas getting rid of formulas
. com wrote:

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

On Fri, 3 Feb 2006 10:59:16 -0800, saveas getting rid of formulas
. com wrote:

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

Thanks

"Ron Rosenfeld" wrote:

On Fri, 3 Feb 2006 09:11:54 -0800, saveas getting rid of formulas
. com wrote:

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


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

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.



"Ron Rosenfeld" wrote:

On Fri, 3 Feb 2006 10:59:16 -0800, saveas getting rid of formulas
. com wrote:

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

Thanks

"Ron Rosenfeld" wrote:

On Fri, 3 Feb 2006 09:11:54 -0800, saveas getting rid of formulas
. com wrote:

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


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

On Fri, 3 Feb 2006 12:27:27 -0800, "Brian Marco"
wrote:

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