Move the formula one cell automatically

[bencyp]

Hi Guys,

I have a sheet that contains Column having dates of the future
I am linking the cell below todays date to another excel sheet from where data is fetched. Its like this ='[data.xlsm]Sheet1'!$AE$54. This link is on say D5 under today's date.

I want to move this link to E5 when the date is changed. i.e. If I open the sheet tomorrow then the (='[data.xlsm]Sheet1'!$AE$54) should be on E5 and the value which was there in D5 should be pasted there as value and remove the link.

I hope I am clear to this point.

 

[bencyp]

Maybe I can explain in different way.

I have a sheet (Sheet1) in which the value in D2 to D5 are generated daily based on some calculation.

I have another sheet (Sheet2) in which there are column with dates.
Now, if today is 25th March then the value from Sheet1 should be copied under 25th March. Then tomorrow if I open the sheet then the value from sheet 1 should be copied under 26th March and so on.

Either it can take the date automatically or can take from a cell where I feed the current date.

 

[John Davis]

Maybe I can explain in different way.

I have a sheet (Sheet1) in which the value in D2 to D5 are generated daily based on some calculation.

I have another sheet (Sheet2) in which there are column with dates.
Now, if today is 25th March then the value from Sheet1 should be copied under 25th March. Then tomorrow if I open the sheet then the value from sheet 1 should be copied under 26th March and so on.

Either it can take the date automatically or can take from a cell where I feed the current date.

I don't really understand your set up (SAMPLE DATA would help). but I think somehow, you could compare the Column with dates on sheet 2 with a helper column which only has the formula =TODAY(). If they match, then maybe use an IF, SUMIF to extract the values from Sheet 1?

 

[bencyp]

Lets see this with an example
I have a sheet that contains following Rows and columns.
.......A.......B......C.....D.....E......F.....G....H....I....J
1......25th 26th 27th 28th 29th
2................................................................. 55
3..................................................................90


In J2 I have 55, and J3 as 90. This data is linked to some other excel sheet.

Now, what I want is if today's date is 25th then the J2 value should go to A2 and J3 value should go to A3. and if I open tomorrow then the value of J2 should go to B2 and J3 should go to B3 and so on on each day.
J2 and J3 is having formula so when that value is going to A2 and A3 then only value should be pasted and not the formula.

Let me know if I am still not clear.

 

[John Davis]

Lets see this with an example
I have a sheet that contains following Rows and columns.
.......A.......B......C.....D.....E......F.....G....H....I....J
1......25th 26th 27th 28th 29th
2................................................................. 55
3..................................................................90


In J2 I have 55, and J3 as 90. This data is linked to some other excel sheet.

Now, what I want is if today's date is 25th then the J2 value should go to A2 and J3 value should go to A3. and if I open tomorrow then the value of J2 should go to B2 and J3 should go to B3 and so on on each day.
J2 and J3 is having formula so when that value is going to A2 and A3 then only value should be pasted and not the formula.

Let me know if I am still not clear.

Does this help?

In A2
=IF(A1=TODAY(),$J$2,"")

In A3
=IF(A1=TODAY(),$J$3,"")

Copy accross for each date in Row1?

 

[bencyp]

This will only help for one day. Tomorrow the date changes and where yesterdays' data was there that will go black. I want to have yesterdays' data as well and move one column everyday.

 

[al_b_cnu]

Hi,

John Davis was nearly right - (too many anchors)
Try:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">25/03/2011</td><td style="text-align: right;;">26/03/2011</td><td style="text-align: right;;">27/03/2011</td><td style="text-align: right;;">28/03/2011</td><td style="text-align: right;;">29/03/2011</td><td style="text-align: right;;">30/03/2011</td><td style="text-align: right;;">31/03/2011</td><td style="text-align: right;;">01/04/2011</td><td style="text-align: right;;">02/04/2011</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">50</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">99</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">99</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=A1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=B1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=C1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=D1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=E1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=F1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1</th><td style="text-align:left">=G1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1</th><td style="text-align:left">=H1+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=IF(<font color="Blue">A$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">B$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">C$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">D$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IF(<font color="Blue">E$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">F$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">G$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=IF(<font color="Blue">H$1=TODAY(<font color="Red"></font>),$J2,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A3</th><td style="text-align:left">=IF(<font color="Blue">A$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">B$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=IF(<font color="Blue">C$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=IF(<font color="Blue">D$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=IF(<font color="Blue">E$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=IF(<font color="Blue">F$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=IF(<font color="Blue">G$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=IF(<font color="Blue">H$1=TODAY(<font color="Red"></font>),$J3,""</font>)</td></tr></tbody></table></td></tr></table><br />