Scratching Head!

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" <Gordon@discussions.microsoft.com> wrote in message
news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> 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" <Gordon@discussions.microsoft.com> wrote in message
> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> > 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" <Gordon@discussions.microsoft.com> wrote in message
news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
> 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" <Gordon@discussions.microsoft.com> wrote in message
>> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
>> > 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" <Gordon@discussions.microsoft.com> wrote in message
> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
> 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" <Gordon@discussions.microsoft.com> wrote in message
>> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
>> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
>> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
news:FC3D506B-A5FE-45B3-A873-76521C6C3285@microsoft.com...
> 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" <Gordon@discussions.microsoft.com> wrote in message
>> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
>> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
>> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
>> >> >> > 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...

mailme@gordoncartwright.co.uk

"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" <Gordon@discussions.microsoft.com> wrote in message
> news:FC3D506B-A5FE-45B3-A873-76521C6C3285@microsoft.com...
> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> >> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
news:3F7304AF-8FB1-4185-A933-029419428049@microsoft.com...
> 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...
>
> mailme@gordoncartwright.co.uk
>
> "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" <Gordon@discussions.microsoft.com> wrote in message
>> news:FC3D506B-A5FE-45B3-A873-76521C6C3285@microsoft.com...
>> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
>> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
>> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
>> >> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> news:3F7304AF-8FB1-4185-A933-029419428049@microsoft.com...
> > 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...
> >
> > mailme@gordoncartwright.co.uk
> >
> > "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" <Gordon@discussions.microsoft.com> wrote in message
> >> news:FC3D506B-A5FE-45B3-A873-76521C6C3285@microsoft.com...
> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
> >> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
> >> >> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
> >> >> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
news:A5549459-E590-4A99-9F24-4227148CD0BC@microsoft.com...
> 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" <Gordon@discussions.microsoft.com> wrote in message
>> news:3F7304AF-8FB1-4185-A933-029419428049@microsoft.com...
>> > 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...
>> >
>> > mailme@gordoncartwright.co.uk
>> >
>> > "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" <Gordon@discussions.microsoft.com> wrote in message
>> >> news:FC3D506B-A5FE-45B3-A873-76521C6C3285@microsoft.com...
>> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> >> news:0257A185-8E20-4E7F-9F42-83F10153FA62@microsoft.com...
>> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> >> >> news:69A17DCD-EE5F-4AA9-B5EC-ACFDDFABDB2F@microsoft.com...
>> >> >> >> > 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" <Gordon@discussions.microsoft.com> wrote in message
>> >> >> >> >> news:31B7F16F-49F7-4B90-B392-81786F90CF65@microsoft.com...
>> >> >> >> >> > 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...
>> >> >> >> >> >
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>