External File Reference

[ArtPRivera]

Hi! Here's one I haven't been able to figure out but would save myself a lot of time... so I pose it to the board

I'm referencing cells in other files I have... something like:

='C:\Hello\[I Can't Figure This Out 11-05-2005.xls]Help'!$A$1

I archive this file so I can track the data day to day. Is there any way to "auto-fill" the new files other than changing the file name by hand?

So I could drag the cell down and get:

='C:\Hello\[I Can't Figure This Out 11-06-2005.xls]Help'!$A$1

Thanks!

 

[tactps]

Assuming that you have "11-06-2005" in column A:

1. Press Alt-F11 to bring up VBA
2. Double-click on the sheet you want to add this to
3. Paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 and target.row > 1 Then
Target.Formula = "='C:\Hello\[I Can't Figure This Out " & Target.Offset(0, -1).Text & ".xls]Help'!$A$1"
endif
End Sub

 

[ArtPRivera]

Thanks for the help!

The dates I have are going across in row 2 in the sheet:

A2 = 11/01/2005, B2 = 11/02/2005, etc.

How do I change the VBA code to match this?

I tried putting this in one of the cells:

="='C:\APR\[Filename "&A2&".xls]Test'!$A29"

All it returned was the string that I wanted to put, but it didn't actually evaluate what was in that external cell reference. Is there any way to make it work in this fashion or do I need to use VBA?

Thanks!

 

[tactps]

You could use:
=indirect("'C:\APR\[Filename "&A2&".xls]Test'!$A29"

but you'd need to open each workbook up for this to work.

VBA is a better idea.

 

[ArtPRivera]

That'll work too - Thanks!