I wish to use the PULL function as part of a formula but am having
problems doing this. Also notice that the PULL function sometimes does
not work. Am using Excel 2000. The following will occassionally work
but at other times return an error #VALUE!

=pull("'"&B4&"["&B1&".xls]"&B3&"'!K70")

If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")

Am I missing something or using the function incorrectly?

By the way B4=path, B1=workbook name, B3 and I1 = sheet name.

I need this to work with both open and closed workbooks but not sure if
the function actually allows me to use with formulas. I am assuming
also that I have the latest code from Harlan. Can anyone help?

Are you busy with Excel, or with SQL Server

"Sandyl" wrote:

I wish to use the PULL function as part of a formula but am having
problems doing this. Also notice that the PULL function sometimes does
not work. Am using Excel 2000. The following will occassionally work
but at other times return an error #VALUE!

=pull("'"&B4&"["&B1&".xls]"&B3&"'!K70")

If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")

Am I missing something or using the function incorrectly?

By the way B4=path, B1=workbook name, B3 and I1 = sheet name.

I need this to work with both open and closed workbooks but not sure if
the function actually allows me to use with formulas. I am assuming
also that I have the latest code from Harlan. Can anyone help?

Kassie,

Am using Excel, hence the question.

Harlan has tweaked his code a few times.

I think that this is the latest version:
http://groups.google.co.uk/groups?se...wsranger.co m

Harlan has posted a link to his FTP site a couple of times:
ftp://members.aol.com/hrlngrv/

You could check there, too.

Sandyl wrote:

I wish to use the PULL function as part of a formula but am having
problems doing this. Also notice that the PULL function sometimes does
not work. Am using Excel 2000. The following will occassionally work
but at other times return an error #VALUE!

=pull("'"&B4&"["&B1&".xls]"&B3&"'!K70")

If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")

Am I missing something or using the function incorrectly?

By the way B4=path, B1=workbook name, B3 and I1 = sheet name.

I need this to work with both open and closed workbooks but not sure if
the function actually allows me to use with formulas. I am assuming
also that I have the latest code from Harlan. Can anyone help?

--

Dave Peterson

Dave,

Thanks for the link - now getting data from the pull function but still
getting the error when using within the formula above. Am I missing
the obvious (more than likely!)?

I guess your formula looks a bit strange to me.

Did you really mean to check G6, then use K70 in your formula?

If you build the formula manually (not using =indirect()), what's returned?

Maybe you have your folder/file/sheet references in the wrong spot????



Sandyl wrote:

Dave,

Thanks for the link - now getting data from the pull function but still
getting the error when using within the formula above. Am I missing
the obvious (more than likely!)?

--

Dave Peterson

"Sandyl" wrote...
....
but at other times return an error #VALUE!
....
If I now try to do the following, I get same problem:

=IF(pull("'"&B4&"["&B1&".xls]"&I1&"'!g6"),
pull("'"&B4&"["&B1&".xls]"&B3&"'!K70"),"")
....

Basic formula debugging necessary.

You're changing worksheet name and cell address. Step 1 is entering the
formulas

="='"&B4&"["&B1&".xls]"&I1&"'!g6"

and

="='"&B4&"["&B1&".xls]"&B3&"'!K70"

Then copy them and paste special as values in other cells, then Edit
Replace = with = in those cells. What do the literal external references
return?

First part of formula is checking for true or false on sheet1 (G6) and
if true entering value of cell K70 from sheet2 - in this case it is a
date but am using formulas for various purposes so only wanted to know
if PULL can be used in this way as I don't want to continually type in
the filename of each spreadsheet (this will continually grow).

I should point out that all my formulas work correctly if using
complete file name and path so no problem there. Just trying to
understand how to use the PULL function and see if this solves my
problems.

Sandyl wrote...
....
. . . so only wanted to know
if PULL can be used in this way as I don't want to continually type in
the filename of each spreadsheet (this will continually grow).
....

pull can be used the same way as any built-in function can be used, so

=IF(pull(OneThing),pull(SomethingElse),"")

should work.

Harlan,

Many thanks for the confirmation. Tried this but got a #value error as
I initially stated. Will try again and ensure that it isn't a simple
typo!

Sandyl wrote...
Many thanks for the confirmation. Tried this but got a #value error
as
I initially stated. Will try again and ensure that it isn't a simple
typo!

So, are you going to respond to the debugging questions I asked two
replies ago? The pull functions works for me. If it doesn't for you,
the odds are the error(s) is(are) on your end.

"Sandyl" wrote in message oups.com...
Harlan,

Many thanks for the confirmation. Tried this but got a #value error as
I initially stated. Will try again and ensure that it isn't a simple
typo!

Harlan,

Sorry didn't specifically answer. Yes I debugged and got the result
expected. Still having problems but will persevere and advise on the
outcome shortly. Day job gets in the way!

Thanks,

Sandy