populate & sum numeric data based on two dates and sheet name using options

[Abdo]

Hello
I have two sheets contains data .
what I want in OUTPUT sheet when write dates in D3,E3 and sheet name in F3 and select optionbutton1 or optiobutton2 (ACTIVE X)
then should show data from row7 by summing the numbers within two dates and sheet name and optionbuttons .
the dates should match with column B in any sheet and the optionbutton will match with column D
my format date DD/MM/YYYY

ABB.xlsm
	A	B	C	D	E
1	ITEM	DATE	REF	CONDITION	BALANCE
2	1	01/01/2023	RE45666	AA	1200.00
3	2	01/01/2023	RE45667	PP	2000.00
4	3	01/01/2023	RE45668	AA	2200.00
5	4	01/01/2023	RE45669	PP	1000.00
6	5	01/01/2023	RE45670	AA	1500.00
7	6	02/01/2023	RE45671	AA	2000.00
8	7	02/01/2023	RE45672	AA	2200.00
9	8	02/01/2023	RE45673	AA	2400.00
10	9	02/01/2023	RE45674	PP	2600.00
CCR

ABB.xlsm
	A	B	C	D	E
1	ITEM	DATE	REF	CONDITION	BALANCE
2	1	01/01/2023	CV455-900	PP	1,200.00
3	2	01/01/2023	CV455-901	PP	2,000.00
4	3	01/01/2023	CV455-902	AA	2,200.00
5	4	01/01/2023	CV455-903	PP	1,000.00
6	5	01/01/2023	CV455-904	PP	1,500.00
7	6	02/01/2023	CV455-905	PP	2,000.00
8	7	02/01/2023	CV455-906	AA	2,200.00
9	8	02/01/2023	CV455-907	AA	2,400.00
CVF

ABB.xlsm
	A	B	C	D	E	F
1
2				FROM DATE	TO DATE	SHEET NAME
3
4
5
OUTPUT

the result in row7 after writing into cells and select optionbutton

ABB.xlsm
	C	D	E	F
1
2		FROM DATE	TO DATE	SHEET NAME
3		01/01/2023	02/01/2023	CCR
4
5
6
7		ITEM	BALANCE
8		1	11500
OUTPUT

the data could be 4000 rows for each sheet
thanks in advance

 

[awoohaw]

What is the significance of Row7?

 

[Abdo]

I don't understand your question !

 

[awoohaw]

I don't understand your question !

You seem to want to return only ROW 7 based on this part of your question:

then should show data from row7 by summing the numbers within two dates and sheet name

is this the only ROW you want used in your calculation:

 

[Abdo]

is this the only ROW you want used in your calculation

absolutely not what I want it !
based on my details will write in cells from date ,to date and select option button then will sum within dates and optionbutton.
I'm really surprised from you don't understand my threads !

 

[awoohaw]

Hey, the forum isn't paid, and to get angry and rude comments from people is uncalled for and not appreciated. We are excel fans that want to share our experience and knowledge with people asking for help. Additionally, you have no say in how I interpret what you have written, if I don't understand I do not understand. I asked a simple question. And, I'm asking again. Please tell me what the underlined portion of your original statement means:

have two sheets contains data .
what I want in OUTPUT sheet when write dates in D3,E3 and sheet name in F3 and select optionbutton1 or optiobutton2 (ACTIVE X)
then should show data from row7 by summing the numbers within two dates and sheet name and optionbuttons .
the dates should match with column B in any sheet and the optionbutton will match with column D
my format date DD/MM/YYYY

 

[Abdo]

Hey, the forum isn't paid

I know .

and to get angry and rude comments from people is uncalled for and not appreciated

you misunderstood !
this is not disrespect from me, when I say you don't understand me I meant to ask me by different way to I understand you if you don't understand my pictures ,details .
so no need comments like this .
after I check my details should be in E7 , should not I say row7 .

 

[awoohaw]

The punctuation and words you used were not considerate. I am guessing English is not your first language (I can not communicate communicate technically in my second language so congratulations to you for doing more). Usually the "!" indicates excitement - good or bad, and in this case the tone was mean and impolite. Sometime words written in different order changes the tone of a statement.

So, regarding the row7 and E7 misunderstanding. I do not see a cell E7 in any of your worksheets that is populated with a formula or value. I am guessing you mean cell E8 on the OUTPUT sheet. I am also guessing you want the total of CONDITION "AP" or "AA" balances for each day (inclusive) in the period for whichever worksheet you want the data to come from.

I am not going to build an Active X control, I am just using Cell C3 to capture the CONDITION.
INPUTS:
C3: CONDITION ("AP" or "AA")
D3: START DATE (Inclusive)
E3: END DATE (Inclusive)
F3: WORKSHEET NAME

D8: It looks like you are asking for a count of something, but I don't know what since you have 1 as an expected value.
But, if using using the same criteria as above I come up with 6 using this formula:
=SUM(
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)*
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)*
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3)
)


E8: It looks like you want the SUM of the filtered values based upon the criteria INPUTS above.
I came up with this formula for cell E8:
=SUM(
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)*
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)*
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3)*
((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$E$2:$E$4000,CVF!$E$2:$E$4000)))
)

I will let you figure out how to map the ACTIVE X values for CONDITION into the formula (where "C3" is underlined and bold in each formula I suggest).

Book1
	A	B	C	D	E	F	G	H
1
2			condition	FROM DATE	TO DATE	SHEET NAME
3			AA	01-01-2023	02-01-2023	CCR
4
5
6
7				ITEM	BALANCE
8				6	11500
9
OUTPUT

Cell Formulas
Range	Formula
D8	D8	=SUM(((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)* ((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)* ((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3))
E8	E8	=SUM(((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))>=D3)* ((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$B$2:$B$4000,CVF!$B$2:$B$4000))<=E3)* ((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$D$2:$D$4000,CVF!$D$2:$D$4000))=C3)* ((CHOOSE(MATCH(F3,{"CCR","CVF"},0),CCR!$E$2:$E$4000,CVF!$E$2:$E$4000))))

Cells with Data Validation
Cell	Allow	Criteria
C3	List	AA,PP
F3	List	CCR,CVF

Best Wishes.

 

[Abdo]

I am guessing English is not your first language

That's correct, sorry about bad expression !

So, regarding the row7 and E7 misunderstanding. I do not see a cell E7 in any of your worksheets that is populated with a formula or value.

sorry this is my bad !

I am guessing you mean cell E8 on the OUTPUT sheet

right

I am not going to build an Active X control, I am just using Cell C3 to capture the CONDITION.

no problem.

based on your picture yes I want it , but unfortunately gives me 2200 , not 11500 !

 

[awoohaw]

based on your picture yes I want it , but unfortunately gives me 2200 , not 11500


I don't know what to tell you. I am pretty sure I am using your cell reference positions and your sheet names and your data. I did have a typo error earlier when I wrote AP instead of AA in my write up but not in the worksheet.

May I suggest you shorten your lookup ranges (temporarily) to just 10 or 15 rows. And, then use the Formula Tracker to see why the values are not coming up.

And why don't you post an xl2bb of your OUTPUT worksheet that shows the calculation is 2200?