Retrieving Cell Data From Variable Files

D

DJ Pomeroy

Okay, this is what I'm trying to do.

I would like to create a log that would track changes from other files. This
log file would know the file names based on other information in the log. I
would like to do it this way so I won't have to change every line of the
log. Is there a way to place a type of "=d2" formula when looking into
another file?

For example, For Cell A2, I want it to get the file from:

C:\Files\Subfolder\FileName.xls

"Subfolder" will be whatever is listed in cell D2
"File Name" will actually be what is listed in cell E2 + cell F2
And then inside that file, I'll want it to come back with Cell A1 (or
whatever).

Is this possible?
 
B

Bernie Deitrick

DJ,

Note that the INDIRECT method won't work on closed files. But one way that will work is to create
the referencing formulas with formulas, and then using a macro to convert to actual formulas. For
example, to pull cell D2:


In cell A2, use the formula

="='C:\Files\" & D2 & "\[" & E2 & "]" &F2 & "'!D2"

In cell D2, put the foldername.

In cell E2, put the filename, including the .xls

In cell F2, put the sheet name

Then copy those cells down as far as you need.

Then select the cells in column F, and run this macro:


Sub ConvStringToFormula()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Text
Next
End Sub

HTH,
Bernie
MS Excel MVP
 
B

bj

Smooth, I like it
Bernie Deitrick said:
DJ,

Note that the INDIRECT method won't work on closed files. But one way that will work is to create
the referencing formulas with formulas, and then using a macro to convert to actual formulas. For
example, to pull cell D2:


In cell A2, use the formula

="='C:\Files\" & D2 & "\[" & E2 & "]" &F2 & "'!D2"

In cell D2, put the foldername.

In cell E2, put the filename, including the .xls

In cell F2, put the sheet name

Then copy those cells down as far as you need.

Then select the cells in column F, and run this macro:


Sub ConvStringToFormula()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Text
Next
End Sub

HTH,
Bernie
MS Excel MVP



DJ Pomeroy said:
Okay, this is what I'm trying to do.

I would like to create a log that would track changes from other files. This log file would know
the file names based on other information in the log. I would like to do it this way so I won't
have to change every line of the log. Is there a way to place a type of "=d2" formula when looking
into another file?

For example, For Cell A2, I want it to get the file from:

C:\Files\Subfolder\FileName.xls

"Subfolder" will be whatever is listed in cell D2
"File Name" will actually be what is listed in cell E2 + cell F2
And then inside that file, I'll want it to come back with Cell A1 (or whatever).

Is this possible?
Click to expand...
Click to expand...
 
Top