Using PULL function

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

I'd be grateful if someone could show me an example of Harlan Grove's PULL function works for a path reference that has been formed from components in several cells:

A1 = " 'C:/work/"
B1 = "data
C1 = "[ClosedBook.xls]Sheet1'!$A1"

D1 = A1 & B1 & C1

I have tried E1=PULL(D1), to obtain the value in the cell referenced in D1, but it doesn't work, and the way the argument is passed to the function is not very clear from the instructions on the page where the code is posted:

http://groups.google.co.uk/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd

Many thanks.

vcoder
 
Last edited:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,577
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is your string concatenation similar to the example?
Highlight the formula and compare it to the example.

Possibly

A1 =C:\work\
B1 = data\
C1 = [ClosedBook.xls]Sheet1'!$A1

Cell with formula ="'"&A1&B1&C1
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,805
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
There was lost backslash in B1: “data\

But using of events suggested earlier is more efficiently because the time & memory consumptions for creating of new Excel instance are not required.
Learn more about events on CPearson site

After solving of issue please mark it in duplicate threat to save the other helper’s time.

Regards,
 
Last edited:

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Thanks Dave and Vladimir for your help.

Just in case anyone else stumbles upon this thread, I thought it may be helpful to close it off with some working examples. The pull function can be applied in the example below as follows:

A1 =C:\work\
B1 = data\
C1 = [ClosedBook.xls]Sheet1'!$A1

D1 = "'" & A1 & B1 & C1

E1 = PULL("'"&A1&B1&C1)

OR

E1 = PULL(D1)

The way the split is applied does not seem to matter, so A1 could hold just C:\ and B1 the remainder of the filepath + filename and these cells could then be concatenated within the pull function to yield the value in the external, closed, worksheet.

It works just great and many thanks to Harlan Grove for the code in the PULL function.

vcoder
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Just wanted to add that I am using a number of PULL functions on my worksheet (close to 100) and it's taking a long time to recalculate the cells after opening the sheet.

I also notice that when I perform certain modifications to the workbook (like removing a worksheet) this kicks off another cycle of recalculations for the PULL functions. It takes more than 5 mins to recalc the full set of statements.

I wonder if there is a way to embed the PULL statements in a field array expression, which may display the results faster. It seems that having several individual PULL statements invokes the function in a serial fashion, whereas embedding in an array - if possible - will run recalcs in batch.

Has anyone tried this?

Many thanks,

vcoder
 

Watch MrExcel Video

Forum statistics

Threads
1,123,397
Messages
5,601,434
Members
414,450
Latest member
Cassy_sn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top