software

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.

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
=quot;='quot;amp;A2 amp; quot;[quot; amp; B2 amp; quot;]quot; amp;C2 amp; quot;'!quot; amp;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 MVPquot;shakey1181quot; gt; wrote in message
news
gt; Apologies for my second post of the day, but I have just discovered this
gt; resource.
gt;
gt; The cell I am working in references data held in a file with a date-based
gt; name. Is there any way to write the formula so when you drag it to other
gt; cells, it goes up in equal incrimemts? So if A1 is referencing '01.01.06' how
gt; do I set it so A2 references '02.01.06' without having to rewrite the formula
gt; for each one?
gt;
gt; Thanks again.
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.
quot;Bernie Deitrickquot; wrote:

gt; Shakey,
gt;
gt; One way is to create the referencing formulas with formulas, and then using a macro to convert to
gt; actual formulas. For example:
gt;
gt; In cell A2, put the folder path:
gt; C:\Excel\Folder\
gt;
gt; In cell B2, put the filename
gt; 02.01.06.xls
gt;
gt; In cell C2, put the sheetname:
gt; Sheet2
gt;
gt; In cell D2, put the cell address:
gt; A2
gt;
gt; in Cell F2, put the formula
gt; =quot;='quot;amp;A2 amp; quot;[quot; amp; B2 amp; quot;]quot; amp;C2 amp; quot;'!quot; amp;D2
gt;
gt; Then copy those cells down as far as you need, and properly increment the dates in column B. (You
gt; can use a formula to do so - just make sure that the returned value is a string.).
gt;
gt; Then select the cells in column F, and run this macro:
gt;
gt; Sub ConvStringToFormula()
gt; Dim myCell As Range
gt; For Each myCell In Selection
gt; myCell.Formula = myCell.Text
gt; Next
gt; End Sub
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;shakey1181quot; gt; wrote in message
gt; news
gt; gt; Apologies for my second post of the day, but I have just discovered this
gt; gt; resource.
gt; gt;
gt; gt; The cell I am working in references data held in a file with a date-based
gt; gt; name. Is there any way to write the formula so when you drag it to other
gt; gt; cells, it goes up in equal incrimemts? So if A1 is referencing '01.01.06' how
gt; gt; do I set it so A2 references '02.01.06' without having to rewrite the formula
gt; gt; for each one?
gt; gt;
gt; gt; Thanks again.
gt;
gt;
gt;

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 MVPquot;shakey1181quot; gt; wrote in message
...
gt; when i run this it returns an error within the macro:
gt;
gt; myCell.Formula = myCell.Text
gt;
gt; I don't know macros very well, but any thoughts would be great.
gt;
gt;
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; Shakey,
gt;gt;
gt;gt; One way is to create the referencing formulas with formulas, and then using a macro to convert to
gt;gt; actual formulas. For example:
gt;gt;
gt;gt; In cell A2, put the folder path:
gt;gt; C:\Excel\Folder\
gt;gt;
gt;gt; In cell B2, put the filename
gt;gt; 02.01.06.xls
gt;gt;
gt;gt; In cell C2, put the sheetname:
gt;gt; Sheet2
gt;gt;
gt;gt; In cell D2, put the cell address:
gt;gt; A2
gt;gt;
gt;gt; in Cell F2, put the formula
gt;gt; =quot;='quot;amp;A2 amp; quot;[quot; amp; B2 amp; quot;]quot; amp;C2 amp; quot;'!quot; amp;D2
gt;gt;
gt;gt; Then copy those cells down as far as you need, and properly increment the dates in column B.
gt;gt; (You
gt;gt; can use a formula to do so - just make sure that the returned value is a string.).
gt;gt;
gt;gt; Then select the cells in column F, and run this macro:
gt;gt;
gt;gt; Sub ConvStringToFormula()
gt;gt; Dim myCell As Range
gt;gt; For Each myCell In Selection
gt;gt; myCell.Formula = myCell.Text
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt;
gt;gt; quot;shakey1181quot; gt; wrote in message
gt;gt; news
gt;gt; gt; Apologies for my second post of the day, but I have just discovered this
gt;gt; gt; resource.
gt;gt; gt;
gt;gt; gt; The cell I am working in references data held in a file with a date-based
gt;gt; gt; name. Is there any way to write the formula so when you drag it to other
gt;gt; gt; cells, it goes up in equal incrimemts? So if A1 is referencing '01.01.06' how
gt;gt; gt; do I set it so A2 references '02.01.06' without having to rewrite the formula
gt;gt; gt; for each one?
gt;gt; gt;
gt;gt; gt; Thanks again.
gt;gt;
gt;gt;
gt;gt;
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 MVPquot;shakey1181quot; gt; wrote in message
...
gt; when i run this it returns an error within the macro:
gt;
gt; myCell.Formula = myCell.Text
gt;
gt; I don't know macros very well, but any thoughts would be great.
gt;
gt;
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; Shakey,
gt;gt;
gt;gt; One way is to create the referencing formulas with formulas, and then using a macro to convert to
gt;gt; actual formulas. For example:
gt;gt;
gt;gt; In cell A2, put the folder path:
gt;gt; C:\Excel\Folder\
gt;gt;
gt;gt; In cell B2, put the filename
gt;gt; 02.01.06.xls
gt;gt;
gt;gt; In cell C2, put the sheetname:
gt;gt; Sheet2
gt;gt;
gt;gt; In cell D2, put the cell address:
gt;gt; A2
gt;gt;
gt;gt; in Cell F2, put the formula
gt;gt; =quot;='quot;amp;A2 amp; quot;[quot; amp; B2 amp; quot;]quot; amp;C2 amp; quot;'!quot; amp;D2
gt;gt;
gt;gt; Then copy those cells down as far as you need, and properly increment the dates in column B.
gt;gt; (You
gt;gt; can use a formula to do so - just make sure that the returned value is a string.).
gt;gt;
gt;gt; Then select the cells in column F, and run this macro:
gt;gt;
gt;gt; Sub ConvStringToFormula()
gt;gt; Dim myCell As Range
gt;gt; For Each myCell In Selection
gt;gt; myCell.Formula = myCell.Text
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt;
gt;gt; quot;shakey1181quot; gt; wrote in message
gt;gt; news
gt;gt; gt; Apologies for my second post of the day, but I have just discovered this
gt;gt; gt; resource.
gt;gt; gt;
gt;gt; gt; The cell I am working in references data held in a file with a date-based
gt;gt; gt; name. Is there any way to write the formula so when you drag it to other
gt;gt; gt; cells, it goes up in equal incrimemts? So if A1 is referencing '01.01.06' how
gt;gt; gt; do I set it so A2 references '02.01.06' without having to rewrite the formula
gt;gt; gt; for each one?
gt;gt; gt;
gt;gt; gt; Thanks again.
gt;gt;
gt;gt;
gt;gt;