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...
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>