formula/autocomplete to link data from other worksheets
 

I need help writing a formula that will link a workbook file with thousands
of other workbooks files by using a formula using a standard file name
convention.

Here is the standard file naming convention:

'W:\Projects\ &A169 &" " &B169 & "\Financial Information\Monthly
Summaries\[" &A169 &" Monthly Summary.xls]Sheet1'!$F$12)

By using this formula I would like to use the auto fill feature to update
the folder/file references.

I have literally thousands of excel files that i need this master workbook
to link to. Entering in the values manually will take far to long to do.

Please help

formula/autocomplete to link data from other worksheets
 

Andrew,

Select cell C169, and run this macro:

Sub MakeLinksForAndrew()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='W:\Projects\" & Cells(myCell.Row, 1).Value & " " & _
Cells(myCell.Row, 2).Value & "\Financial Information\Monthly
Summaries\[" & _
Cells(myCell.Row, 1) & "&" Monthly Summary.xls]Sheet1'!$F$12)"
Next myCell
End Sub

are created.

HTH,
Bernie
MS Excel MVP

"Andrew" wrote in message
...
I need help writing a formula that will link a workbook file with thousands
of other workbooks files by using a formula using a standard file name
convention.

Here is the standard file naming convention:

'W:\Projects\ &A169 &" " &B169 & "\Financial Information\Monthly
Summaries\[" &A169 &" Monthly Summary.xls]Sheet1'!$F$12)

By using this formula I would like to use the auto fill feature to update
the folder/file references.

I have literally thousands of excel files that i need this master workbook
to link to. Entering in the values manually will take far to long to do.

Please help

formula/autocomplete to link data from other worksheets
 

Thanks for the quick response

I am getting a syntax error with this formula

I am using excel 2003 - if that helps

"Bernie Deitrick" wrote:

Andrew,

Select cell C169, and run this macro:

Sub MakeLinksForAndrew()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='W:\Projects\" & Cells(myCell.Row, 1).Value & " " & _
Cells(myCell.Row, 2).Value & "\Financial Information\Monthly
Summaries\[" & _
Cells(myCell.Row, 1) & "&" Monthly Summary.xls]Sheet1'!$F$12)"
Next myCell
End Sub

are created.

HTH,
Bernie
MS Excel MVP

"Andrew" wrote in message
...
I need help writing a formula that will link a workbook file with thousands
of other workbooks files by using a formula using a standard file name
convention.

Here is the standard file naming convention:

'W:\Projects\ &A169 &" " &B169 & "\Financial Information\Monthly
Summaries\[" &A169 &" Monthly Summary.xls]Sheet1'!$F$12)

By using this formula I would like to use the auto fill feature to update
the folder/file references.

I have literally thousands of excel files that i need this master workbook
to link to. Entering in the values manually will take far to long to do.

Please help

formula/autocomplete to link data from other worksheets
 

Andrew,

Well, I did not have your folder and file structure available for testing.
;-)

To fix the code so that it works, open one of your files, create a working
link to that sheet and cell, then close the file to get Excel to show the
entire path in the link. Copy the link as text (use F2, then select the
whole formula) and paste it into your code, surrounded by quotes. Then try
your code, without modification of the formula. You should end up with,
basically, this:

Sub Test()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = _
"=WORKING LINK FORMULA FROM CELL"
Next myCell
End Sub


When you get that to work, begin replacing the bits that correspond to the
contents of the cells in column A and column B (file path and name?) with:

" & Cells(myCell.Row, 1).Value & "
or
" & Cells(myCell.Row, 2).Value & "

then try the code again, making sure that you are on the correct row when
you run the code.

HTH,
Bernie
MS Excel MVP


"Andrew" wrote in message
...
Thanks for the quick response

I am getting a syntax error with this formula

I am using excel 2003 - if that helps

"Bernie Deitrick" wrote:

Andrew,

Select cell C169, and run this macro:

Sub MakeLinksForAndrew()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='W:\Projects\" & Cells(myCell.Row, 1).Value & " " & _
Cells(myCell.Row, 2).Value & "\Financial Information\Monthly
Summaries\[" & _
Cells(myCell.Row, 1) & "&" Monthly Summary.xls]Sheet1'!$F$12)"
Next myCell
End Sub

are created.

HTH,
Bernie
MS Excel MVP

"Andrew" wrote in message
...
I need help writing a formula that will link a workbook file with
thousands
of other workbooks files by using a formula using a standard file name
convention.

Here is the standard file naming convention:

'W:\Projects\ &A169 &" " &B169 & "\Financial Information\Monthly
Summaries\[" &A169 &" Monthly Summary.xls]Sheet1'!$F$12)

By using this formula I would like to use the auto fill feature to
update
the folder/file references.

I have literally thousands of excel files that i need this master
workbook
to link to. Entering in the values manually will take far to long to
do.

Please help

formula/autocomplete to link data from other worksheets
 

I fixed the compile error

I think there was an extra &" in your macro third line from the bottom

However now i am getting an application defined error

I double checked the file and path info and it's all ok and works fine when
entered manually.

Do you know what could be going on here?

Again, thanks for your help
"Andrew" wrote:

Thanks for the quick response

I am getting a syntax error with this formula

I am using excel 2003 - if that helps

"Bernie Deitrick" wrote:

Andrew,

Select cell C169, and run this macro:

Sub MakeLinksForAndrew()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='W:\Projects\" & Cells(myCell.Row, 1).Value & " " & _
Cells(myCell.Row, 2).Value & "\Financial Information\Monthly
Summaries\[" & _
Cells(myCell.Row, 1) & "&" Monthly Summary.xls]Sheet1'!$F$12)"
Next myCell
End Sub

are created.

HTH,
Bernie
MS Excel MVP

"Andrew" wrote in message
...
I need help writing a formula that will link a workbook file with thousands
of other workbooks files by using a formula using a standard file name
convention.

Here is the standard file naming convention:

'W:\Projects\ &A169 &" " &B169 & "\Financial Information\Monthly
Summaries\[" &A169 &" Monthly Summary.xls]Sheet1'!$F$12)

By using this formula I would like to use the auto fill feature to update
the folder/file references.

I have literally thousands of excel files that i need this master workbook
to link to. Entering in the values manually will take far to long to do.

Please help

formula/autocomplete to link data from other worksheets
 

Got it to work! Please ignore all follow up posts my me above

Thanks!

"Bernie Deitrick" wrote:

Andrew,

Select cell C169, and run this macro:

Sub MakeLinksForAndrew()
Dim myCell As Range

For Each myCell In Selection
myCell.Formula = _
"='W:\Projects\" & Cells(myCell.Row, 1).Value & " " & _
Cells(myCell.Row, 2).Value & "\Financial Information\Monthly
Summaries\[" & _
Cells(myCell.Row, 1) & "&" Monthly Summary.xls]Sheet1'!$F$12)"
Next myCell
End Sub

are created.

HTH,
Bernie
MS Excel MVP

"Andrew" wrote in message
...
I need help writing a formula that will link a workbook file with thousands
of other workbooks files by using a formula using a standard file name
convention.

Here is the standard file naming convention:

'W:\Projects\ &A169 &" " &B169 & "\Financial Information\Monthly
Summaries\[" &A169 &" Monthly Summary.xls]Sheet1'!$F$12)

By using this formula I would like to use the auto fill feature to update
the folder/file references.

I have literally thousands of excel files that i need this master workbook
to link to. Entering in the values manually will take far to long to do.

Please help

formula/autocomplete to link data from other worksheets
 

Great! Thanks for letting us know that you worked it out.

I had a problem with my first post: I had noted that if you select all the
cells in column C where you want to have links, the code will cycle through
all of the cells that are selected, creating a link in each one.

Somehow, I deleted that part just before I sent the message :-(

Bernie

Got it to work! Please ignore all follow up posts my me above

Thanks!