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]

Hi, why 10 of May 2015, should it be set to 12 days ago or should it be yesterday and the past 6 days?

 

[cyrilbrd]

In case date is today...

Excel 2013
	A	B	C	D	E	F	G
1	RAW_DATA - WHAT I READ FROM				RAW_DATA DISPLAY
2	Date	Amount	Total		Date	Faults	No
3	5/12/2015	123	1		5/17/2015	128	6
4	5/13/2015	124	2		5/18/2015	129	7
5	5/14/2015	125	3		5/19/2015	130	8
6	5/15/2015	126	4		5/20/2015	131	9
7	5/16/2015	127	5		5/21/2015	132	10
8	5/17/2015	128	6		5/22/2015	133	11
9	5/18/2015	129	7
10	5/19/2015	130	8
11	5/20/2015	131	9
12	5/21/2015	132	10
13	5/22/2015	133	11
Mireille

Cell Formulas
Range	Formula
E3	=E4-1 copied down till E7
E8	=Today()
F3	=INDEX(B$3:B$20,MATCH($E3,$A$3:$A$20,0))
G3	=INDEX(C$3:C$20,MATCH($E3,$A$3:$A$20,0))

 

[Mirelle]

Cool, I see how that works. Thanx

Now how would I do it for the following: I am showing week information (YYYY/MM)

Next week information will be 2015/21, 21 and 18 May-25 May 2015. So the 2015/15 will now show the info under it 2015/16.

	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/15	15	06 Apr-12 Apr 2015	3221	5
4	2015/12	12	16 Mar-22 Mar 2015	564	2341		2015/16	16	13 Apr-19 Apr 2015	8956	8956
5	2015/13	13	23 Mar-29 Mar 2015	0	67		2015/17	17	20 Apr-26 Apr 2015	2	2
6	2015/14	14	30 Mar-05 Apr 2015	32	685		2015/18	18	27 Apr-03 May 2015	32	32
7	2015/15	15	06 Apr-12 Apr 2015	3221	5		2015/19	19	04 May-10 May 2015	456	784
8	2015/16	16	13 Apr-19 Apr 2015	8956	8956		2015/20	20	11 May-17 May 2015	233	234
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

<tbody>
</tbody>

Tried to make it look like yours, but clearly not working. How do you get your info to keep lines and fill color?

Thanx
Mirelle

 

[cyrilbrd]

What is a week? Monday to Monday, Monday to Sunday or from 1st to 7th then from 8th to 14th ect...

 

[cyrilbrd]

here:

 

[Mirelle]

The week works from Monday to Sunday.

11 May-17 May 2015

 

[cyrilbrd]

The week works from Monday to Sunday.

11 May-17 May 2015

noted, would the dynamic cell be in G8 hence FINKWK?

 

[Mirelle]

I would say so. G8 will be the cell that would point to the new FINWK in the RAW_DATA

The FINWK would go up to 2015/52 (1 year's worth of weeks)

 

[cyrilbrd]

mmmh noted, will get back to you as soon as I get something satisfactory