Home |
Search |
Today's Posts |
#1
|
|||
|
|||
PULL FUNCTION
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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!)? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
"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? |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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! |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
"Harlan Grove" wrote in message ups.com...
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. Harlan, Sorry, thought I had. I did check formulas with full path and filename then tried PULL but got same result. As I stated previously, I will now look more closely at my typing to ensure that syntax is correct. Many thanks for your generous help to date. |
#13
|
|||
|
|||
"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 |
#14
|
|||
|
|||
"Harlan Grove" wrote in message ups.com...
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. Harlan, Just so that there is no misunderstanding, I have de-bugged and the filename and path are correct. Now trying to figure out why it won't work with my formula. As you say, it is more than likely a typo on my part. Will keep you informed. Thanks for everything - I really appreciate it. |
#16
|
|||
|
|||
Sandy wrote...
.... Have now made progress. Unfortunately, this has highlighted a couple of problems: 1. If I pull a date from an open workbook, it responds with the wrong base (i.e. 15 Apr 05 returns 16 Apr 09). Close the workbook and I then get the right date. While this looks like a 1900 vs 1904 date system issue, I can't replicate this problem. If I enter a date in a cell in one workbook using 1904 date system then access that cell in another workbook using 1900 date system using either pull or a literal external reference link, I get a date 4 years and 1 day before what appears in the first workbook. If you get something different, please show the actual contents of the date cell in your first workbook and the pull formula you're using to access it from the second workbook. IF YOU WANT HELP, **YOU** MUST SHOW FORMULAS AND DATA. 2. Can't actually auto update with Pull. Should it? Bit of a major problem if it does not. Do you mean check for changes in closed files? How would they change? Are you accessing files on a network that you don't have open but other users do and are constantly making changes? If you just mean updating values from other workbooks when you open a file referring to ranges in them, then if you have Calculation set to automatic, Excel recalcs the pull calls even though it doesn't display the update dialog that it displays when there are literal external reference links in the opened workbook. So what exactly do you mean by 'auto update'? 3. When copying and pasting to multiple cells, I must then enter each cell formula and physicallly press the Enter key to update the cell contents. I can't replicate this behavior. If X:\Y\[foo.xls]Sheet1!A1:A10 contains the formula =ROW() in each cell, then entering the formula =pull("'X:\Y\[foo.xls]Sheet1'!A"&ROW()) in [bar.xls]Sheet1!C1, copying and pasting this cell into [bar.xls]Sheet1!C2:C10 results in {1;2;3;4;5;6;7;8;9;10} on my PC, though it takes some time for the formulas to recalc. Does Excel display the Calculate indicator in the status bar after you've pasted cells containing pull calls? If so, you have to let Excel recalc. Note that pull can take a while to recalc when called from many cells. One thing it isn't is fast. |
#17
|
|||
|
|||
"Harlan Grove" wrote in message roups.com...
Sandy wrote... ... Have now made progress. Unfortunately, this has highlighted a couple of problems: 1. If I pull a date from an open workbook, it responds with the wrong base (i.e. 15 Apr 05 returns 16 Apr 09). Close the workbook and I then get the right date. While this looks like a 1900 vs 1904 date system issue, I can't replicate this problem. If I enter a date in a cell in one workbook using 1904 date system then access that cell in another workbook using 1900 date system using either pull or a literal external reference link, I get a date 4 years and 1 day before what appears in the first workbook. If you get something different, please show the actual contents of the date cell in your first workbook and the pull formula you're using to access it from the second workbook. IF YOU WANT HELP, **YOU** MUST SHOW FORMULAS AND DATA. 2. Can't actually auto update with Pull. Should it? Bit of a major problem if it does not. Do you mean check for changes in closed files? How would they change? Are you accessing files on a network that you don't have open but other users do and are constantly making changes? If you just mean updating values from other workbooks when you open a file referring to ranges in them, then if you have Calculation set to automatic, Excel recalcs the pull calls even though it doesn't display the update dialog that it displays when there are literal external reference links in the opened workbook. So what exactly do you mean by 'auto update'? 3. When copying and pasting to multiple cells, I must then enter each cell formula and physicallly press the Enter key to update the cell contents. I can't replicate this behavior. If X:\Y\[foo.xls]Sheet1!A1:A10 contains the formula =ROW() in each cell, then entering the formula =pull("'X:\Y\[foo.xls]Sheet1'!A"&ROW()) in [bar.xls]Sheet1!C1, copying and pasting this cell into [bar.xls]Sheet1!C2:C10 results in {1;2;3;4;5;6;7;8;9;10} on my PC, though it takes some time for the formulas to recalc. Does Excel display the Calculate indicator in the status bar after you've pasted cells containing pull calls? If so, you have to let Excel recalc. Note that pull can take a while to recalc when called from many cells. One thing it isn't is fast. Harlan, First, let me clarify exactly where I am. The workbook with the external references is used as a "management overview" of numerous workbooks that are used by other staff on the network to update customer returns - and indeed create new workbooks. I have been using literal external links and associated formulas with no problem. What I wanted to do was to be able to easily update the "management" workbook simply without having to copy and paste full filenames then modify for specific entries. Given that some of my formulas have up to 4 nested If's, this is quite onerous. Now to the behaviour I am presently experiencing: 1. The date issue does not occur if I use literal filenames. It only occurs when I use the pull function and if the second workbook is OPEN. When I close the workbook and then re-enter the formula, everything is fine. The formula is as follows: =pull("'"&$B$6&"["&A69&".xls]"&$A$3&"'!$g$2") The cell value on the open/closed workbook is: 15/04/2005 I have deliberately removed the IF calculations to ensure that I get same repeatable result. 2. The auto update I refer to is simply the automatic calculation option (sorry about my terminology). This works fine if using full path and filename but not if using the pull function, hence my question. What I have seen is that if I copy and paste a pull formula to multiple cells (for example B5 THRU B25), only the last cell is calculated, even though all fields are updated correctly. Even if I close the workbook, open it and update all links, the other cells are not updated. I must physically enter each cell and update the formula - not an ideal option. I appreciate that I am asking a lot of your time but am running out of ideas as to what can be wrong if you keep stating that all works fine with your workbook. Am loathe to ditch this as your function seems to be the answer to my problems (on paper at least!). Am I missing something??? |
#18
|
|||
|
|||
Sandy wrote...
.... Now to the behaviour I am presently experiencing: 1. The date issue does not occur if I use literal filenames. It only occurs when I use the pull function and if the second workbook is OPEN. When I close the workbook and then re-enter the formula, everything is fine. The formula is as follows: =pull("'"&$B$6&"["&A69&".xls]"&$A$3&"'!$g$2") The cell value on the open/closed workbook is: 15/04/2005 I have deliberately removed the IF calculations to ensure that I get same repeatable result. Now I can replicate this if the source workbook uses the 1904 date system. Specifically, Evaluate seems to return dates in the 1900 date system all the time, and it automatically adjusts for dates in the 1904 date system. As far as I'm concerned, this is a bug in Excel/VBA, but it's one I need to work around. I'll post an update on my AOL ftp space tonight. 2. The auto update I refer to is simply the automatic calculation option (sorry about my terminology). This works fine if using full path and filename but not if using the pull function, hence my question. What I have seen is that if I copy and paste a pull formula to multiple cells (for example B5 THRU B25), only the last cell is calculated, even though all fields are updated correctly. Even if I close the workbook, open it and update all links, the other cells are not updated. I must physically enter each cell and update the formula - not an ideal option. .... What do you mean by 'even though all fields are updated correctly'? I'll assume you mean the argument to pull() is constructed correctly for each call, but when you copy a cell containing a pull() call in its formula and paste into other cells only one of those other cells recalcs correctly. I can't replicate that behavior. If Calculation is Automatic, then formulas calling pull() recalc automatically when pasted into other cells. If Calculation is Manual, then they don't, and you'd need to press [F9] to recalc. If this just doesn't work in your formulas on your PC, there's a quicker way of effectively re-entering a batch of formulas. Select the range containing the formulas and use Edit Replace to replace all = with =, i.e., replace = with itself. |
#19
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
PULL function | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |