Help with INDIRECT function.

D

DaveO

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.
 
P

Pete

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
 
B

Bob Phillips

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)
 
D

DaveO

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.
 
B

Bob Phillips

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)
 
D

DaveO

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.
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
H

Harlan Grove

DaveO said:
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?
Click to expand...
....

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/
 
D

DaveO

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.
 
B

Bob Phillips

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)
 
D

DaveO

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.
 
B

Bob Phillips

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)
 
Top