Help formular excel!!!

[Nguyen Anh Dung]

I have sheet A

and sheet BC

help formular for sheet BC:
with condition B2=>=25/08/2021 to B3<=01/09/2021 (B2,B3 have change input) then input column B,C,D,E,F,G,H,I If sheet A have mark x in about date :25/08/2021 to 01/09/2021.
example Name:

Best regards,
Nguyen Anh Dung

 

[Hyakkivn]

Thật sự chưa hiểu ý bạn đang muốn làm gì. Bảng bạn làm thấy khó hiểu kiểu gì ấy
Cái này thì hiểu :

with condition B2=>=25/08/2021 to B3<=01/09/2021 (B2,B3 have change input) then input column B,C,D,E,F,G,H,I

Còn chỗ này không hiểu

If sheet A have mark x in about date :25/08/2021 to 01/09/2021.

 

[Nguyen Anh Dung]

Thật sự chưa hiểu ý bạn đang muốn làm gì. Bảng bạn làm thấy khó hiểu kiểu gì ấy
Cái này thì hiểu :

Còn chỗ này không hiểu

Ý mình là trong khoảng thời gian từ 25/8 đến 1/9 nếu những công việc bên bảng A có làm đánh dấu x thì bên bảng BC mình sẽ đánh dấu x vô trong khoảng thời gian đó.
Cám ơn!!!

 

[jorismoerings]

Hi,

Try this on BC sheet:

Book1
	A	B	C	D	E	F	G
4		A	B	c	d	e	f
5	Test1	X	X
BC

Cell Formulas
Range	Formula
B5:G5	B5	=IF(SUMPRODUCT((A!$B$3:$B$18=$A5)*(A!$D$3:$I$18="X")*(A!$C$3:$C$18>=$B$2)*(A!$C$3:$C$18<=$B$3)*(A!$D$2:$I$2=B$4))>0,"X","")

 

[Nguyen Anh Dung]

Hi,

Try this on BC sheet:

Book1
	A	B	C	D	E	F	G
4		A	B	c	d	e	f
5	Test1	X	X
BC

Cell Formulas
Range	Formula
B5:G5	B5	=IF(SUMPRODUCT((A!$B$3:$B$18=$A5)*(A!$D$3:$I$18="X")*(A!$C$3:$C$18>=$B$2)*(A!$C$3:$C$18<=$B$3)*(A!$D$2:$I$2=B$4))>0,"X","")

Thanks you so much!!!

 

[Peter_SSs]

@Hyakkivn @Nguyen Anh Dung

Please post in English only in this forum, per the forum instruction detail:

If you want to post or answer questions in another language then use the Questions in Other Languages forum

 

[Hyakkivn]

@Hyakkivn @Nguyen Anh Dung

Please post in English only in this forum, per the forum instruction detail:
View attachment 46204

If you want to post or answer questions in another language then use the Questions in Other Languages forum

I'm so sorry !!! I will keep it in mind Thanks for the notice

 

[Peter_SSs]

I'm so sorry !!! I will keep it in mind

No problem, just remember for the future.

 

[Nguyen Anh Dung]

Hi,

Try this on BC sheet:

Book1
	A	B	C	D	E	F	G
4		A	B	c	d	e	f
5	Test1	X	X
BC

Cell Formulas
Range	Formula
B5:G5	B5	=IF(SUMPRODUCT((A!$B$3:$B$18=$A5)*(A!$D$3:$I$18="X")*(A!$C$3:$C$18>=$B$2)*(A!$C$3:$C$18<=$B$3)*(A!$D$2:$I$2=B$4))>0,"X","")

help me in this case:
sheet A i have insert column KetQua

I want result for sheet BC column KetQua by the last time

Thanks you and Best regards!!!
Nguyen Anh Dung

 

[jorismoerings]

Hi,

It's much nicer and easier for us if you don't change your request. It's better to insert all your needs at once so we can take them into account.
This is not like a chat function so if you have any additional request please open a new thread or as mentioned before insert all your needs at once.

Having said that, try this:

		A		B		C		D		E
Test1	25-8-2021			x	No
Test1	26-8-2021
Test1	27-8-2021			x	No	x
Test1	28-8-2021			x	No			x
Test1	1-9-2021			x	Yes
Test2	25-8-2021	x	No
Test2	26-8-2021
Test2	27-8-2021	x	No
Test2	28-8-2021	x	No
Test2	1-9-2021

Change.xlsx
	A	B	C	D	E	F	G
2		25-8-2021
3		1-9-2021
4		A		B		c
5	Test1			X	Yes	X
6	Test2	X	No
7
bc

Cell Formulas
Range	Formula
F5,B5:B6,D5:D6	B5	=IF(SUMPRODUCT((a!$B$3:$B$12=$A5)*(a!$D$3:$M$12="X")*(a!$C$3:$C$12>=$B$2)*(a!$C$3:$C$12<=$B$3)*(a!$D$2:$M$2=B$4))>0,"X","")
C5:C6,E5:E6	C5	=IF(B5="X",INDEX(a!E$3:E$12,MAX((a!$B$3:$B$12=bc!$A5)*(a!$C$3:$C$12>=bc!$B$2)*(a!$C$3:$C$12<=bc!$B$3)*(a!D$3:D$12="X")*(ROW(a!$B$3:$B$12)-2))),"")