Auto filling when changing some value

[dani_koentz]

Hi,

i have frequency in column A and start date in column B and from column C1 until AD1 it contains date (date is set on weekly basis).
i need some help on excel formula as i change frequency in column A, the value in C2 until AD5 will change according to the frequency and start date.
if the frequency was set on monthly then the result will repeat every month and if set on week then it will repeat weekly.
thank you,

 

[SanjayGMusafir]

Update your current Version of Excel and Platform you are using. Solution would depend on that.

 

[Joe4]

Update your current Version of Excel and Platform you are using. Solution would depend on that.

He means to update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). The link will take you there.

 

[dani_koentz]

hello,

i had updated in the account details.
i was using microsoft 365 and running in windows
thank you.

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F	G
1	Frequency	Start	1/4/23	1/11/23	1/18/23	1/25/23	2/1/23
2	Week	4-Jan
Sheet1

Cell Formulas
Range	Formula
C1:BB1	C1	=TRANSPOSE(IFS($A$2="Week",SEQUENCE(52,,$B$2,7),$A$2="Month",EDATE($B$2,SEQUENCE(12,,0,1)),TRUE,0))
Dynamic array formulas.

 

[SanjayGMusafir]

Check this and revert. It keeps start to be Monday and goes on -

Book1
	A	B	C	D
1	Frequency	Start	1/2/23	2/2/23
2	Month	4-Jan
3
4
Sheet1

Cell Formulas
Range	Formula
C1:N1	C1	=TRANSPOSE(LET(sDt,$B$2-WEEKDAY($B$2,2)+1,IFS($A$2="Week",SEQUENCE(52,,sDt,7),$A$2="Month",EDATE(sDt,SEQUENCE(12,,0,1)),TRUE,0)))
Dynamic array formulas.

 

[dani_koentz]

Hello Sanjay,

thank you.
your formula works. for defining the result.
however what i looking for is comparing between result from row 2 based on your formula and row 1.
row 1 --> date in C1 until end, this is set manually. i set this date start from monday.
in row 2 frequency i will set whether week, month, quarter or year; including start date. after finished set this 2 item then in C2 will give result based on your formula.
if in A2 i set the frequency week then the result from C2:end need to compare with C1:end and if the result was same within the week then it will give "OK" and it repeat in every week and will start from near start date.
if in A2 i set the frequency month then the result from C2:end need to compare with C1:end and if the result was same within the week then it will give "OK" and it repeat in every month.
as you can see in picture i sent, in row 3, i set the start date in Feb 19 then result "OK" start from H3 because the start date was within the week of Feb 13 and it will repeat weekly.

i hope my explanation can help you to understand what was i looking for.

 

[SanjayGMusafir]

I have understood your requirement...

Please share XL2BB for you data to understand the data structure and formulae you are using to pull data.