Referencing Other Files - Formula Pattern?

[shakey1181]

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.

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[shakey1181]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[Bernie Deitrick]

Shakey,

Sounds like you have an error in your formula as generated. Check your entries in A2, B2, C2, and
D2 - make sure that spaces are correct, spelling is exact, etc.

You can check your formula generation by selecting the cell with the formula, pressing F2, selecting
the entire formula in the formula edit bar, then pressing F9 and then finally pressing enter. (You
can always press Undo to get back to the original formula.) If your formula works doing that, it
should work with the macro.

HTH,
Bernie
MS Excel MVP


"shakey1181" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

[Bernie Deitrick]

Shakey,

A good way to check your formula generation is to open the file, select your other file, type = in a
blank cell, select the source file, source sheet, and source cell, press enter, then close the
source file. The link formula should then look like

='C:\Excel\Folder\[01.05.06.xls]Sheet3'!A3

which should exactly match what is shown in your cell with the formula.

HTH,
Bernie
MS Excel MVP


"shakey1181" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>