when i run this it returns an error within the macro:

myCell.Formula = myCell.Text

I don't know macros very well, but any thoughts would be great.



"Bernie Deitrick" wrote:

Shakey,

One way is to create the referencing formulas with formulas, and then using a macro to convert to
actual formulas. For example:

In cell A2, put the folder path:
C:\Excel\Folder\

In cell B2, put the filename
02.01.06.xls

In cell C2, put the sheetname:
Sheet2

In cell D2, put the cell address:
A2

in Cell F2, put the formula
="='"&A2 & "[" & B2 & "]" &C2 & "'!" &D2

Then copy those cells down as far as you need, and properly increment the dates in column B. (You
can use a formula to do so - just make sure that the returned value is a string.).

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


"shakey1181" wrote in message
...
Apologies for my second post of the day, but I have just discovered this
resource.

The cell I am working in references data held in a file with a date-based
name. Is there any way to write the formula so when you drag it to other
cells, it goes up in equal incrimemts? So if A1 is referencing '01.01.06' how
do I set it so A2 references '02.01.06' without having to rewrite the formula
for each one?

Thanks again.