Auto move Selection

[chaboyski]

Hi Team,

just wanted to check if there is a formula or a macro code that i can use if i wanted to automatically move the cell selection depending on the date.

sample below:

cell A1 includes validation and this would determine upto what date would be selected
cells C1 to G1 would be hard coded data
Cells i2 - m2 would have the formula that would be moving, example is if it may 8 on A1 is selected, the data from cells i2 - m2 would only select and calculate upto b2 to b9.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	9-May		data1	data2	data3	data4	data5		Sum data1	Sum data2	Sum data3	Sum data4	Sum data5
2		1-May	123	123	123	123	123		1107	1107	1107	1107	1107
3		2-May	123	123	123	123	123
4		3-May	123	123	123	123	123
5		4-May	123	123	123	123	123
6		5-May	123	123	123	123	123
7		6-May	123	123	123	123	123
8		7-May	123	123	123	123	123
9		8-May	123	123	123	123	123
10		9-May	123	123	123	123	123
11		10-May
12		11-May
13		12-May
14		13-May
15		14-May
16		15-May
Sheet1

Cell Formulas
Range	Formula
I2:M2	I2	=SUM(C2:C10)

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$B$2:$B$16

Thank you in advanced.

 

[kevin9999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	8-May		data1	data2	data3	data4	data5		Sum data1	Sum data2	Sum data3	Sum data4	Sum data5
2		1-May	123	123	123	123	123		984	984	984	984	984
3		2-May	123	123	123	123	123
4		3-May	123	123	123	123	123
5		4-May	123	123	123	123	123
6		5-May	123	123	123	123	123
7		6-May	123	123	123	123	123
8		7-May	123	123	123	123	123
9		8-May	123	123	123	123	123
10		9-May	123	123	123	123	123
11		10-May
12		11-May
13		12-May
14		13-May
15		14-May
16		15-May
Sheet1

Cell Formulas
Range	Formula
I2:M2	I2	=SUMIF($B$2:$B$16,"<="&$A$1,C2:C16)

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$B$2:$B$16

 

[chaboyski]

Thank you @kevin9999

what if the Data in A1 is in another sheet? tried it but it gave me an error. but i just moved the date to the same sheet so it would work. thank you!

 

[kevin9999]

Thank you @kevin9999

what if the Data in A1 is in another sheet? tried it but it gave me an error. but i just moved the date to the same sheet so it would work. thank you!

Happy to help, and thanks for the feedback