Update formula

[Mirelle]

Good morning


Not to sure what you would call what I want done. I have been on Google for awhile, but no luck in finding what I want. Well, I did find you guys.


Your help would be appreciated.

RAW_DATA - WHAT I READ FROM

Date	Amount	Total
2015/05/01	123	1
2015/05/02	124	2
2015/05/03	125	3
2015/05/04	126	4
2015/05/05	127	5
2015/05/06	128	6
2015/05/07	129	7
2015/05/08	130	8
2015/05/09	131	9
2015/05/10	132	10
2015/05/11	133	11

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

RAW_DATA DISPLAY

Date	Faults	No
2015/05/05	127	5
2015/05/06	128	6
2015/05/07	129	7
2015/05/08	130	8
2015/05/09	131	9
2015/05/10	132	10

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

I have daily data (RAW_DATA - WHAT I READ FROM). When I show my information(RAW_DATA DISPLAY)I only want to show the last day plus 5 days past.


So it shows 2015/05/10 to 2015/05/05


Tomorrow a new day will be added to my raw_data, so now I want to show 2015/05/11 to 2015/05/06


So 2015/05/05 will change to 2015/05/06 and the last date will be 2015/05/11. Each date will move one up.


Formula in 2015/05/05 = Sheet1!A6, so now it must change to =Sheet1!A7

Thanx you

Mirelle

 

[cyrilbrd]

try

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K
1	RAW_DATA - WHAT I READ FROM						RAW_DATA DISPLAY
2	FINWK	WK NO	WK	Call1	Call2		FINWK	WK NO	WK	Call1	Call2
3	2015/11	11	09 Mar-15 Mar 2015	654	432		2015/16	16	13 Apr-19 Apr 2015	8956	8956
4	2015/12	12	16 Mar-22 Mar 2015	564	2341		2015/17	17	20 Apr-26 Apr 2015	2	2
5	2015/13	13	23 Mar-29 Mar 2015	0	67		2015/18	18	27 Apr-03 May 2015	32	32
6	2015/14	14	30 Mar-05 Apr 2015	32	685		2015/19	19	04 May-10 May 2015	456	784
7	2015/15	15	06 Apr-12 Apr 2015	3221	5		2015/20	20	11 May-17 May 2015	233	234
8	2015/16	16	13 Apr-19 Apr 2015	8956	8956		2015/21
9	2015/17	17	20 Apr-26 Apr 2015	2	2
10	2015/18	18	27 Apr-03 May 2015	32	32
11	2015/19	19	04 May-10 May 2015	456	784
12	2015/20	20	11 May-17 May 2015	233	234
Sheet4

Cell Formulas
Range	Formula
G3	=YEAR((TODAY()-ROWS(G3:G$7)*7)-WEEKDAY((TODAY()-ROWS(G3:G$7)*7)-1)+4)&"/"&INT(((TODAY()-ROWS(G3:G$7)*7)-DATE(YEAR((TODAY()-ROWS(G3:G$7)*7)-WEEKDAY((TODAY()-ROWS(G3:G$7)*7)-1)+4),1,3)+WEEKDAY(DATE(YEAR((TODAY()-ROWS(G3:G$7)*7)-WEEKDAY((TODAY()-ROWS(G3:G$7)*7)-1)+4),1,3))+5)/7) copied down till G7
G8	=YEAR((TODAY()-ROWS(G8:G$8))-WEEKDAY((TODAY()-ROWS(G8:G$8))-1)+4)&"/"&INT(((TODAY()-ROWS(G8:G$8))-DATE(YEAR((TODAY()-ROWS(G8:G$8))-WEEKDAY((TODAY()-ROWS(G8:G$8))-1)+4),1,3)+WEEKDAY(DATE(YEAR((TODAY()-ROWS(G8:G$8))-WEEKDAY((TODAY()-ROWS(G8:G$8))-1)+4),1,3))+5)/7))
H3	=INDEX(B$3:B$20,MATCH($G3,$A$3:$A$20,0)) copied right and down till needed

 

[Mirelle]

Hi

Thank you very much.

It is not working for me or I am just stupid.

I have a cell with formula in : =A6

Now I need formula or code that would replace the 6 with a 7 to show : =A7

At the moment I do that change by hand

 

[cyrilbrd]

Hi

Thank you very much.

It is not working for me or I am just stupid.

I have a cell with formula in : =A6

Now I need formula or code that would replace the 6 with a 7 to show : =A7

At the moment I do that change by hand

No worries, what doesn't work? What version of Excel are you using?

 

[Mirelle]

Windows 7, Excel 2013

I just want to remove manual interference. Changing the A6 to A7.

I see your code points to date, what if there is no date?

Mirelle

 

[cyrilbrd]

My solution is based on your model and works fine here, the formulae does not use any cell with date, but a reference to today.

 

[Mirelle]

As soon as I add the next weeks info in A13 it does nothing. It does not update week 2015/21 to week 2015/22

 

[cyrilbrd]

As soon as I add the next weeks info in A13 it does nothing. It does not update week 2015/21 to week 2015/22

That is because we are week 20/2015 no?

 

[cyrilbrd]

That is because we are week 20/2015 no?

Or do you want to consider the last date inputted in the system?

 

[Mirelle]

Each week the information changes. I already have week 2015/21 information and when I add the formula to it, it does not populate.

 

[cyrilbrd]

Each week the information changes. I already have week 2015/21 information and when I add the formula to it, it does not populate.

The formula I gave you will compute based on actual week, today we are 20th week...