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

Abdo

Board Regular
Joined
May 16, 2022
Messages
201
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
ABCDE
1ITEMDATEREFCONDITIONBALANCE
2101/01/2023RE45666AA1200.00
3201/01/2023RE45667PP2000.00
4301/01/2023RE45668AA2200.00
5401/01/2023RE45669PP1000.00
6501/01/2023RE45670AA1500.00
7602/01/2023RE45671AA2000.00
8702/01/2023RE45672AA2200.00
9802/01/2023RE45673AA2400.00
10902/01/2023RE45674PP2600.00
CCR



ABB.xlsm
ABCDE
1ITEMDATEREFCONDITIONBALANCE
2101/01/2023CV455-900PP1,200.00
3201/01/2023CV455-901PP2,000.00
4301/01/2023CV455-902AA2,200.00
5401/01/2023CV455-903PP1,000.00
6501/01/2023CV455-904PP1,500.00
7602/01/2023CV455-905PP2,000.00
8702/01/2023CV455-906AA2,200.00
9802/01/2023CV455-907AA2,400.00
CVF


ABB.xlsm
ABCDEF
1
2FROM DATETO DATESHEET NAME
3
4
5
OUTPUT


Z.PNG


the result in row7 after writing into cells and select optionbutton
ABB.xlsm
CDEF
1
2FROM DATETO DATESHEET NAME
301/01/202302/01/2023CCR
4
5
6
7ITEMBALANCE
8111500
OUTPUT


x.PNG

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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
1699087006501.png
 
Upvote 0
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 !:rolleyes:
 
Upvote 0
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
 
Upvote 0
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 .
 
Upvote 0
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
ABCDEFGH
1
2conditionFROM DATETO DATESHEET NAME
3AA01-01-202302-01-2023CCR
4
5
6
7ITEMBALANCE
8611500
9
OUTPUT
Cell Formulas
RangeFormula
D8D8=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))
E8E8=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
CellAllowCriteria
C3ListAA,PP
F3ListCCR,CVF


Best Wishes.
 
Upvote 0
Solution
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 !:eek:
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 !:rolleyes:
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top