Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

Hi...

250 values per file, in exactly the same cells. The files are randomly
named, though inside each file in a fixed cell there is a random string
containing a random product number; I have been able to extract both the file
path for each file and the random product number within the random string.

I had tried to map the table using indirect.ext but I just get #value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list for this.

And in cell D1, enter your cell address or name that you want to create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your 'blank' table, and run this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are randomly
named, though inside each file in a fixed cell there is a random string
containing a random product number; I have been able to extract both the file
path for each file and the random product number within the random string.

I had tried to map the table using indirect.ext but I just get #value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in column A),
so I can rejoin them with your code to include brackets and single commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list for this.

And in cell D1, enter your cell address or name that you want to create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your 'blank' table, and run this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are randomly
named, though inside each file in a fixed cell there is a random string
containing a random product number; I have been able to extract both the file
path for each file and the random product number within the random string.

I had tried to map the table using indirect.ext but I just get #value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values, and delete columns A and B. Not
sure what to do after that - depends on the population of your list (is it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in column A),
so I can rejoin them with your code to include brackets and single commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list for this.

And in cell D1, enter your cell address or name that you want to create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your 'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are randomly
named, though inside each file in a fixed cell there is a random string
containing a random product number; I have been able to extract both the file
path for each file and the random product number within the random string.

I had tried to map the table using indirect.ext but I just get #value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

Once I get the first line done then I hope to fill down. Fingers crossed.
You've earned your dinner tonight...thanks.

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values, and delete columns A and B. Not
sure what to do after that - depends on the population of your list (is it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in column A),
so I can rejoin them with your code to include brackets and single commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list for this.

And in cell D1, enter your cell address or name that you want to create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your 'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are randomly
named, though inside each file in a fixed cell there is a random string
containing a random product number; I have been able to extract both the file
path for each file and the random product number within the random string.

I had tried to map the table using indirect.ext but I just get #value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest. Do you mean just the cell address (J12 J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new table)
your 'blank' table, and run macro. Does this go into a module, workbook or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values, and delete columns A and B. Not
sure what to do after that - depends on the population of your list (is it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in column A),
so I can rejoin them with your code to include brackets and single commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list for this.

And in cell D1, enter your cell address or name that you want to create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your 'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are randomly
named, though inside each file in a fixed cell there is a random string
containing a random product number; I have been able to extract both the file
path for each file and the random product number within the random string.

I had tried to map the table using indirect.ext but I just get #value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they need to be found?

There are many ways to make the summary. Do you want links? Do you want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that lists the
directory/path of the 50 files but indirect and indirect.ext is not working.
Any other ideas.

Thanks in advance

Gordon...

Gordon,

Just the cell address goes into row 1.

The macro goes into a codemodule in your workbook.

There are many good guides concerning getting started with macros: Here's
one that I often refer folks to:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The blank cells that you select must have filled in columns A:C in the same
row, and a cell address in row 1 of the same column. You will get that
error if the formula that is being created is invalid.

HTH,
Bernie
MS Excel MVP



"Gordon" wrote in message
...
Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest. Do you mean just the cell address (J12 J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new
table)
your 'blank' table, and run macro. Does this go into a module, workbook or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values, and
delete columns A and B. Not
sure what to do after that - depends on the population of your list (is
it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in
column A),
so I can rejoin them with your code to include brackets and single
commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail
me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list
for this.

And in cell D1, enter your cell address or name that you want to
create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your
'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are
randomly
named, though inside each file in a fixed cell there is a random
string
containing a random product number; I have been able to extract both
the file
path for each file and the random product number within the random
string.

I had tried to map the table using indirect.ext but I just get
#value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets
into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they
need to be found?

There are many ways to make the summary. Do you want links? Do you
want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in
one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that
lists the
directory/path of the 50 files but indirect and indirect.ext is
not working.
Any other ideas.

Thanks in advance

Gordon...

Hi Bernie...

Thanks for all your help so far. Try and try as I might I just can't get
this thing to work. I know this might sound cheeky, but is there any chance
that you have a working example of this onto which I can transpose my own
data. I appreciate that this might be beyond your brief but I would
appreciate it massively...

Many thanks...

Gordon...



"Bernie Deitrick" wrote:

Gordon,

Just the cell address goes into row 1.

The macro goes into a codemodule in your workbook.

There are many good guides concerning getting started with macros: Here's
one that I often refer folks to:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The blank cells that you select must have filled in columns A:C in the same
row, and a cell address in row 1 of the same column. You will get that
error if the formula that is being created is invalid.

HTH,
Bernie
MS Excel MVP



"Gordon" wrote in message
...
Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest. Do you mean just the cell address (J12 J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new
table)
your 'blank' table, and run macro. Does this go into a module, workbook or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values, and
delete columns A and B. Not
sure what to do after that - depends on the population of your list (is
it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in
column A),
so I can rejoin them with your code to include brackets and single
commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never fail
me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current list
for this.

And in cell D1, enter your cell address or name that you want to
create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest.

Then select (for our example, D2:the last cell of your new table) your
'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are
randomly
named, though inside each file in a fixed cell there is a random
string
containing a random product number; I have been able to extract both
the file
path for each file and the random product number within the random
string.

I had tried to map the table using indirect.ext but I just get
#value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or brackets
into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they
need to be found?

There are many ways to make the summary. Do you want links? Do you
want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture in
one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that
lists the
directory/path of the 50 files but indirect and indirect.ext is
not working.
Any other ideas.

Thanks in advance

Gordon...

I'll semd it to you in the morning (New York morning, that is)

Bernie


"Gordon" wrote in message
...
Hi Bernie...

Thanks for all your help so far. Try and try as I might I just can't get
this thing to work. I know this might sound cheeky, but is there any
chance
that you have a working example of this onto which I can transpose my own
data. I appreciate that this might be beyond your brief but I would
appreciate it massively...

Many thanks...

Gordon...



"Bernie Deitrick" wrote:

Gordon,

Just the cell address goes into row 1.

The macro goes into a codemodule in your workbook.

There are many good guides concerning getting started with macros: Here's
one that I often refer folks to:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The blank cells that you select must have filled in columns A:C in the
same
row, and a cell address in row 1 of the same column. You will get that
error if the formula that is being created is invalid.

HTH,
Bernie
MS Excel MVP



"Gordon" wrote in message
...
Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the
cell
addresses or names of interest. Do you mean just the cell address (J12
J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new
table)
your 'blank' table, and run macro. Does this go into a module, workbook
or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values,
and
delete columns A and B. Not
sure what to do after that - depends on the population of your list
(is
it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in
column A),
so I can rejoin them with your code to include brackets and single
commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never
fail
me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current
list
for this.

And in cell D1, enter your cell address or name that you want to
create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest.

Then select (for our example, D2:the last cell of your new table)
your
'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are
randomly
named, though inside each file in a fixed cell there is a random
string
containing a random product number; I have been able to extract
both
the file
path for each file and the random product number within the
random
string.

I had tried to map the table using indirect.ext but I just get
#value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or
brackets
into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they
need to be found?

There are many ways to make the summary. Do you want links? Do
you
want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture
in
one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that
lists the
directory/path of the 50 files but indirect and indirect.ext
is
not working.
Any other ideas.

Thanks in advance

Gordon...

Birnie...

Many thanks. Look forward to receiving it.

Gordon...

Bright and Sunny in Kendal UK

"Bernie Deitrick" wrote:

I'll semd it to you in the morning (New York morning, that is)

Bernie


"Gordon" wrote in message
...
Hi Bernie...

Thanks for all your help so far. Try and try as I might I just can't get
this thing to work. I know this might sound cheeky, but is there any
chance
that you have a working example of this onto which I can transpose my own
data. I appreciate that this might be beyond your brief but I would
appreciate it massively...

Many thanks...

Gordon...



"Bernie Deitrick" wrote:

Gordon,

Just the cell address goes into row 1.

The macro goes into a codemodule in your workbook.

There are many good guides concerning getting started with macros: Here's
one that I often refer folks to:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The blank cells that you select must have filled in columns A:C in the
same
row, and a cell address in row 1 of the same column. You will get that
error if the formula that is being created is invalid.

HTH,
Bernie
MS Excel MVP



"Gordon" wrote in message
...
Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the
cell
addresses or names of interest. Do you mean just the cell address (J12
J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new
table)
your 'blank' table, and run macro. Does this go into a module, workbook
or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values,
and
delete columns A and B. Not
sure what to do after that - depends on the population of your list
(is
it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in
column A),
so I can rejoin them with your code to include brackets and single
commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never
fail
me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current
list
for this.

And in cell D1, enter your cell address or name that you want to
create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest.

Then select (for our example, D2:the last cell of your new table)
your
'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are
randomly
named, though inside each file in a fixed cell there is a random
string
containing a random product number; I have been able to extract
both
the file
path for each file and the random product number within the
random
string.

I had tried to map the table using indirect.ext but I just get
#value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or
brackets
into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they
need to be found?

There are many ways to make the summary. Do you want links? Do
you
want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture
in
one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that
lists the
directory/path of the 50 files but indirect and indirect.ext
is
not working.
Any other ideas.

Thanks in advance

Gordon...

Just sent it...

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Birnie...

Many thanks. Look forward to receiving it.

Gordon...

Bright and Sunny in Kendal UK

"Bernie Deitrick" wrote:

I'll semd it to you in the morning (New York morning, that is)

Bernie


"Gordon" wrote in message
...
Hi Bernie...

Thanks for all your help so far. Try and try as I might I just can't get
this thing to work. I know this might sound cheeky, but is there any
chance
that you have a working example of this onto which I can transpose my own
data. I appreciate that this might be beyond your brief but I would
appreciate it massively...

Many thanks...

Gordon...



"Bernie Deitrick" wrote:

Gordon,

Just the cell address goes into row 1.

The macro goes into a codemodule in your workbook.

There are many good guides concerning getting started with macros: Here's
one that I often refer folks to:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The blank cells that you select must have filled in columns A:C in the
same
row, and a cell address in row 1 of the same column. You will get that
error if the formula that is being created is invalid.

HTH,
Bernie
MS Excel MVP



"Gordon" wrote in message
...
Hi...

All worked swell until the last bit. I need to clarify two statements:

You wrote: Then starting in E1 and going across row 1, enter all the
cell
addresses or names of interest. Do you mean just the cell address (J12
J13
J38 etc) or the whole path/address c:\desktop etc etc.

You wrote: Then select (for our example, D2:the last cell of your new
table)
your 'blank' table, and run macro. Does this go into a module, workbook
or
the sheet code. I get an error when I run it: Run Time Error 1004
Application-Defined or object defined error...

Any clues...so close I can smell it.

Gordon...

"Bernie Deitrick" wrote:

Gordon,

With your string (C:\Desktop\Raw\hammer.xlsSheet1!j12) in cell A2:

In cell B2:
=MID(A2,FIND("#",SUBSTITUTE(A2,"\","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1)+1,200)

In cell C2:
=SUBSTITUTE(A2,"\" &B2,"")

In cell D2:
=LEFT(B2,FIND(".xls",B2)+3)

In cell E2:
=LEFT(SUBSTITUTE(B2,D2,""),FIND("!",SUBSTITUTE(B2, D2,""))-1)

In cell F2:
=MID(B2,FIND("!",B2)+1,200)

Copy these down to match your list, convert the formulas to values,
and
delete columns A and B. Not
sure what to do after that - depends on the population of your list
(is
it all 12,500 links?)


HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
ok...this is great. Just one last thing I need...

How would you seperate my already established paths (currently in
column A),
so I can rejoin them with your code to include brackets and single
commas,
which will then work with indirect.ext

eg

C:\Desktop\Raw\hammer.xlsSheet1!j12

into

C:\Desktop\Raw
hammer.xls
sheet1
j12

Thanks. I did think this would be impossible but you MVP's never
fail
me.

Gordon


"Bernie Deitrick" wrote:

OK.For your example: C:\Desktop\Raw\hammer.xlsSheet1!j12

In cell A2, enter your folder path:

C:\Desktop\Raw

In cell B2, enter your file name:
hammer.xls

In cell C2, enter your sheetname
Sheet1

Create 1 line for each of the files - you could use your current
list
for this.

And in cell D1, enter your cell address or name that you want to
create the link to.
J12

Then starting in E1 and going across row 1, enter all the cell
addresses or names of interest.

Then select (for our example, D2:the last cell of your new table)
your
'blank' table, and run
this
macro:

Sub MakeLinksForGordon()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub

And viola, your links:

='C:\Desktop\Raw\[hammer.xls]Sheet1'!J12

are created.

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

250 values per file, in exactly the same cells. The files are
randomly
named, though inside each file in a fixed cell there is a random
string
containing a random product number; I have been able to extract
both
the file
path for each file and the random product number within the
random
string.

I had tried to map the table using indirect.ext but I just get
#value. The
problem is that the string I have is;

C:\Desktop\Raw\hammer.xlsSheet1!j12

and I don't know how to get the single quotation marks or
brackets
into the
formula as it should look below. But it doesn't work anyway.

=INDIRECT.EXT('C:\Desktop\Raw\[hammer.xls]Sheet1'!j12)

Happy for links or values that don't change.

Thanks for your help so far.

Gordon

"Bernie Deitrick" wrote:

Gordon,

Are there 250 values per file, or 5 from each of 50 files?

Are the values on the same sheet (by name) and cell, or do they
need to be found?

There are many ways to make the summary. Do you want links? Do
you
want values that don't
change?

HTH,
Bernie
MS Excel MVP


"Gordon" wrote in message
...
Hi...

I have 50 files that contain 250 values that I want to capture
in
one file -
capture.xls in a table.

So far I have been able to create a column in capture.xls that
lists the
directory/path of the 50 files but indirect and indirect.ext
is
not working.
Any other ideas.

Thanks in advance

Gordon...