Help with INDIRECT function.
 

Using the INDIRECT function I'm trying to reference data in another Workbook.
However, if that workbook is not open the function returns a #REF! value.
Open the Workbook it's referencing and it happily returns the vlaue I need. I
understand it's one of the volatile functions in Excel, but I don't know why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

If you have not put the full path in the filename, Excel will assume
that the file is in the current directory and look there - if the file
is not in that directory then it is treated as a bad reference (hence
#REF error). Just copy the file into the current folder.

When the file is open it works okay because Excel can find the
reference.

Hope this helps.

Pete

Help with INDIRECT function.
 

Indirect does NOT work with closed workbooks.

--
Don Guillett
SalesAid Software

"DaveO" wrote in message
...
Using the INDIRECT function I'm trying to reference data in another
Workbook.
However, if that workbook is not open the function returns a #REF! value.
Open the Workbook it's referencing and it happily returns the vlaue I
need. I
understand it's one of the volatile functions in Excel, but I don't know
why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

Take a look at Harlan Grove's PULL function that does do that

ftp://members.aol.com/hrlngrv/
Look for pull.zip


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Using the INDIRECT function I'm trying to reference data in another
Workbook.
However, if that workbook is not open the function returns a #REF! value.
Open the Workbook it's referencing and it happily returns the vlaue I
need. I
understand it's one of the volatile functions in Excel, but I don't know
why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

Hi Bob, thanks for the help here on this function.

I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of data
by the looks of it. By this I mean that the pull function needs to return an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?

TIA.

"Bob Phillips" wrote:

Take a look at Harlan Grove's PULL function that does do that

ftp://members.aol.com/hrlngrv/
Look for pull.zip


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Using the INDIRECT function I'm trying to reference data in another
Workbook.
However, if that workbook is not open the function returns a #REF! value.
Open the Workbook it's referencing and it happily returns the vlaue I
need. I
understand it's one of the volatile functions in Excel, but I don't know
why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

No, I think it was just written to handle single cells. I will suggest to
Harlan that he enhances it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Hi Bob, thanks for the help here on this function.

I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of
data
by the looks of it. By this I mean that the pull function needs to return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?

TIA.

"Bob Phillips" wrote:

Take a look at Harlan Grove's PULL function that does do that

ftp://members.aol.com/hrlngrv/
Look for pull.zip


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Using the INDIRECT function I'm trying to reference data in another
Workbook.
However, if that workbook is not open the function returns a #REF!
value.
Open the Workbook it's referencing and it happily returns the vlaue I
need. I
understand it's one of the volatile functions in Excel, but I don't
know
why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

I've spent the past hour or so just going through the code Bob and have found
an answer.

In the code at the very end Harlan has a line that reads ...

pull = r.Value

I've altered this in my code to now read ...

pull = r.Value2

and it allows array values. Perhaps a quick check of the variables (using a
simple IF) would enhance this to be able to be used in all situations.

Also, a quick caveat in case you do speak to him about this. I'd suggest
that somewhere in the code he tries to tell the suer to limit the array that
is returned to an absolute minimum else the function will take a long time to
return a value. Only trying to help out here and certainly don;t wish to seem
ungrateful for what is a great bit of code!

Thanks again!

Dave.



"Bob Phillips" wrote:

No, I think it was just written to handle single cells. I will suggest to
Harlan that he enhances it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Hi Bob, thanks for the help here on this function.

I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of
data
by the looks of it. By this I mean that the pull function needs to return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?

TIA.

"Bob Phillips" wrote:

Take a look at Harlan Grove's PULL function that does do that

ftp://members.aol.com/hrlngrv/
Look for pull.zip


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Using the INDIRECT function I'm trying to reference data in another
Workbook.
However, if that workbook is not open the function returns a #REF!
value.
Open the Workbook it's referencing and it happily returns the vlaue I
need. I
understand it's one of the volatile functions in Excel, but I don't
know
why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

Thanks Dave, I will look at all that and pass it on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
I've spent the past hour or so just going through the code Bob and have
found
an answer.

In the code at the very end Harlan has a line that reads ...

pull = r.Value

I've altered this in my code to now read ...

pull = r.Value2

and it allows array values. Perhaps a quick check of the variables (using
a
simple IF) would enhance this to be able to be used in all situations.

Also, a quick caveat in case you do speak to him about this. I'd suggest
that somewhere in the code he tries to tell the suer to limit the array
that
is returned to an absolute minimum else the function will take a long time
to
return a value. Only trying to help out here and certainly don;t wish to
seem
ungrateful for what is a great bit of code!

Thanks again!

Dave.



"Bob Phillips" wrote:

No, I think it was just written to handle single cells. I will suggest
to
Harlan that he enhances it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Hi Bob, thanks for the help here on this function.

I've read the VBA and gone through it and have a basic understanding
of
whats going on here. However, the PULL function can't handle arrays of
data
by the looks of it. By this I mean that the pull function needs to
return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?

TIA.

"Bob Phillips" wrote:

Take a look at Harlan Grove's PULL function that does do that

ftp://members.aol.com/hrlngrv/
Look for pull.zip


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Using the INDIRECT function I'm trying to reference data in
another
Workbook.
However, if that workbook is not open the function returns a #REF!
value.
Open the Workbook it's referencing and it happily returns the
vlaue I
need. I
understand it's one of the volatile functions in Excel, but I
don't
know
why
it won't return return the value if the other sheet is not open.

Any ideas to help at all please??

TIA.

Help with INDIRECT function.
 

"DaveO" wrote...
....
I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of data
by the looks of it. By this I mean that the pull function needs to return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?
....

Testing is good. Testing would have shown that pull *CAN* return arrays.

The only difference between the .Value and .Value2 properties is that the
former can return values of Date type while the latter converts such values
into Double type. Both evaluate to arrays when applied to multiple cell,
single area ranges.

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

Help with INDIRECT function.
 

Harlan, my apologies it was in the way that I was using it and not the code
itself. I found that out when I had to do further work on it last night.

I do have one last question. The Pull function's speed depends on the amount
of data that it has to return. Obviously I'm trying to get this as slick as
possible, so with a VLOOKUP({Cell}, Pull{}, {Row}, {True/False}) I want to
return only the smallest dataset possible (ie. Only 2 rows of data). I've
tried using this ... A1:A500, C1:C500 ... as my dataset. When I do it throws
a problem, but as it's a custom function I can't get any help. I've stepped
through the VBA and can't see why this shouldn't work, but still at a bit of
loss as to why it's a problem.

Also, I do have 1 question about the code. One line you have in there is
this...

Pull = Evaluate(xref)

Can you explain what this does as I'm at a loss, unless it's to produce an
error code so that you can test what state the function is currently in? It
confused me and an explanation of what it does would really help me.

Again, apologies for the inaccuracy of my comments to Bob, it was a genuine
mistake born of poor understanding.

TIA.

"Harlan Grove" wrote:

"DaveO" wrote...
....
I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of data
by the looks of it. By this I mean that the pull function needs to return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?
....

Testing is good. Testing would have shown that pull *CAN* return arrays.

The only difference between the .Value and .Value2 properties is that the
former can return values of Date type while the latter converts such values
into Double type. Both evaluate to arrays when applied to multiple cell,
single area ranges.

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

Help with INDIRECT function.
 

Evaluate takes an Excel expression and converts it to an object or value in
VBA. So you can build a formula string within VBA and pass that to Evaluate
to get the result, or as I recall happens in this case, it gets the value in
the cell identified in xref. This is in case the workbook is already open,
saving having to start an Excel instance in which to get that cell value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Harlan, my apologies it was in the way that I was using it and not the
code
itself. I found that out when I had to do further work on it last night.

I do have one last question. The Pull function's speed depends on the
amount
of data that it has to return. Obviously I'm trying to get this as slick
as
possible, so with a VLOOKUP({Cell}, Pull{}, {Row}, {True/False}) I want to
return only the smallest dataset possible (ie. Only 2 rows of data). I've
tried using this ... A1:A500, C1:C500 ... as my dataset. When I do it
throws
a problem, but as it's a custom function I can't get any help. I've
stepped
through the VBA and can't see why this shouldn't work, but still at a bit
of
loss as to why it's a problem.

Also, I do have 1 question about the code. One line you have in there is
this...

Pull = Evaluate(xref)

Can you explain what this does as I'm at a loss, unless it's to produce an
error code so that you can test what state the function is currently in?
It
confused me and an explanation of what it does would really help me.

Again, apologies for the inaccuracy of my comments to Bob, it was a
genuine
mistake born of poor understanding.

TIA.

"Harlan Grove" wrote:

"DaveO" wrote...
....
I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of
data
by the looks of it. By this I mean that the pull function needs to
return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?
....

Testing is good. Testing would have shown that pull *CAN* return arrays.

The only difference between the .Value and .Value2 properties is that
the
former can return values of Date type while the latter converts such
values
into Double type. Both evaluate to arrays when applied to multiple cell,
single area ranges.

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

Help with INDIRECT function.
 

Thanks for that Bob, makes sense now. Looked at the Help file but it didn't
really explain it very well.

If I'd known it existed, then I'd have written some of the code I've done
before differently. You learn something new every day.

Thanks.

"Bob Phillips" wrote:

Evaluate takes an Excel expression and converts it to an object or value in
VBA. So you can build a formula string within VBA and pass that to Evaluate
to get the result, or as I recall happens in this case, it gets the value in
the cell identified in xref. This is in case the workbook is already open,
saving having to start an Excel instance in which to get that cell value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Harlan, my apologies it was in the way that I was using it and not the
code
itself. I found that out when I had to do further work on it last night.

I do have one last question. The Pull function's speed depends on the
amount
of data that it has to return. Obviously I'm trying to get this as slick
as
possible, so with a VLOOKUP({Cell}, Pull{}, {Row}, {True/False}) I want to
return only the smallest dataset possible (ie. Only 2 rows of data). I've
tried using this ... A1:A500, C1:C500 ... as my dataset. When I do it
throws
a problem, but as it's a custom function I can't get any help. I've
stepped
through the VBA and can't see why this shouldn't work, but still at a bit
of
loss as to why it's a problem.

Also, I do have 1 question about the code. One line you have in there is
this...

Pull = Evaluate(xref)

Can you explain what this does as I'm at a loss, unless it's to produce an
error code so that you can test what state the function is currently in?
It
confused me and an explanation of what it does would really help me.

Again, apologies for the inaccuracy of my comments to Bob, it was a
genuine
mistake born of poor understanding.

TIA.

"Harlan Grove" wrote:

"DaveO" wrote...
....
I've read the VBA and gone through it and have a basic understanding of
whats going on here. However, the PULL function can't handle arrays of
data
by the looks of it. By this I mean that the pull function needs to
return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?
....

Testing is good. Testing would have shown that pull *CAN* return arrays.

The only difference between the .Value and .Value2 properties is that
the
former can return values of Date type while the latter converts such
values
into Double type. Both evaluate to arrays when applied to multiple cell,
single area ranges.

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

Help with INDIRECT function.
 

It isn't efficient though Dave, be aware of that. I use it mainly it the
same sort of manner that Harlan does, or to use SUMPRODUCT formulae in VBA,
but other than that it is best to be careful. Also, it is wise to tie it to
the sheet being worked on, like so

Activesheet.Evaluate(...

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Thanks for that Bob, makes sense now. Looked at the Help file but it
didn't
really explain it very well.

If I'd known it existed, then I'd have written some of the code I've done
before differently. You learn something new every day.

Thanks.

"Bob Phillips" wrote:

Evaluate takes an Excel expression and converts it to an object or value
in
VBA. So you can build a formula string within VBA and pass that to
Evaluate
to get the result, or as I recall happens in this case, it gets the
value in
the cell identified in xref. This is in case the workbook is already
open,
saving having to start an Excel instance in which to get that cell
value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DaveO" wrote in message
...
Harlan, my apologies it was in the way that I was using it and not the
code
itself. I found that out when I had to do further work on it last
night.

I do have one last question. The Pull function's speed depends on the
amount
of data that it has to return. Obviously I'm trying to get this as
slick
as
possible, so with a VLOOKUP({Cell}, Pull{}, {Row}, {True/False}) I
want to
return only the smallest dataset possible (ie. Only 2 rows of data).
I've
tried using this ... A1:A500, C1:C500 ... as my dataset. When I do it
throws
a problem, but as it's a custom function I can't get any help. I've
stepped
through the VBA and can't see why this shouldn't work, but still at a
bit
of
loss as to why it's a problem.

Also, I do have 1 question about the code. One line you have in there
is
this...

Pull = Evaluate(xref)

Can you explain what this does as I'm at a loss, unless it's to
produce an
error code so that you can test what state the function is currently
in?
It
confused me and an explanation of what it does would really help me.

Again, apologies for the inaccuracy of my comments to Bob, it was a
genuine
mistake born of poor understanding.

TIA.

"Harlan Grove" wrote:

"DaveO" wrote...
....
I've read the VBA and gone through it and have a basic
understanding of
whats going on here. However, the PULL function can't handle arrays
of
data
by the looks of it. By this I mean that the pull function needs to
return
an
array of data as it exists inside of a VLOOKUP function.

Any more ideas at all please?
....

Testing is good. Testing would have shown that pull *CAN* return
arrays.

The only difference between the .Value and .Value2 properties is
that
the
former can return values of Date type while the latter converts such
values
into Double type. Both evaluate to arrays when applied to multiple
cell,
single area ranges.

As for alternatives, try the INDIRECT.EXT function in Laurent
Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/