How to count values under a column date
Closed
ABBIE
-
Jun 1, 2010 at 09:36 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 5, 2010 at 08:14 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 5, 2010 at 08:14 AM
Related:
- How to count values under a column date
- How to count names in excel - Guide
- Based on the values in cells b77 b81 c77 - Excel Forum
- Excel count occurrences of string in column - Guide
- How to delete column in word - Guide
- Based on the values in cells b77 ✓ - Excel Forum
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 2, 2010 at 10:38 AM
Jun 2, 2010 at 10:38 AM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
HI!
Here's how it goes...
I have 3 excel files with data that looks like this:
Excel file 1: (other 2 excel files displays the same datas except product names are different.)
A B C D E F G .....
1 ORDERS of this MONTH
2 Client Product 6/1/10 6/2/10 6/3/10 6/4/10 6/5/10 ......
3 AA P10 1 2 1
4 AB C11 0 1 0
5 AC P10 1 1 1
6 AD P10 0 0 0
Starting C2, D2, E2, F2 and so on..., these are current dates.
I have a vb form in another file which displays the current date in a label, a combo box which have 3 items in list, a textbox and a command button. The form runs and display the current date. When the user click a product category from the 3 product categories listed, the number of clients with zero order for that day should appear in the textbox (in the above excel, the textbox should contain : 2 because today is june 3 and 2 clients have 0 orders) from the excel file for that product...
Product Category 1 in the combobox will extract data from excel1, product category 2 from excel2, product category 3 from excel3.
And when the command button is clicked, another form will appear (current form will be hidden) which will display the names of the client with zero orders in a label or textbox or whichever is applicable (in this case, Client AB and AD should appear in that form).
By the way, I have the rest of the codes running perfectly except that I can't display the count of clients with zero in the textbox of the first form and also I can't display their names in the next form for each product.
All in all, I have 4 forms ( 1 for the main form and the other 3 forms to display the names of each product category (1 to 3)).
Form 1 will display the current time, text box for the count of clients with zero, a combo box for the 3 product categories, command button to view the corresponding form (form2, form 3 or form 4) to the product category
Form 2, 3, 4 to display the names of the clients with zero orders in that category and a command button to close the form.
There should not be any changes in the reference excel files after the execution of the file. These files are updated daily.
Also, is there a way to automatically open the main form when windows start?
Here's how it goes...
I have 3 excel files with data that looks like this:
Excel file 1: (other 2 excel files displays the same datas except product names are different.)
A B C D E F G .....
1 ORDERS of this MONTH
2 Client Product 6/1/10 6/2/10 6/3/10 6/4/10 6/5/10 ......
3 AA P10 1 2 1
4 AB C11 0 1 0
5 AC P10 1 1 1
6 AD P10 0 0 0
Starting C2, D2, E2, F2 and so on..., these are current dates.
I have a vb form in another file which displays the current date in a label, a combo box which have 3 items in list, a textbox and a command button. The form runs and display the current date. When the user click a product category from the 3 product categories listed, the number of clients with zero order for that day should appear in the textbox (in the above excel, the textbox should contain : 2 because today is june 3 and 2 clients have 0 orders) from the excel file for that product...
Product Category 1 in the combobox will extract data from excel1, product category 2 from excel2, product category 3 from excel3.
And when the command button is clicked, another form will appear (current form will be hidden) which will display the names of the client with zero orders in a label or textbox or whichever is applicable (in this case, Client AB and AD should appear in that form).
By the way, I have the rest of the codes running perfectly except that I can't display the count of clients with zero in the textbox of the first form and also I can't display their names in the next form for each product.
All in all, I have 4 forms ( 1 for the main form and the other 3 forms to display the names of each product category (1 to 3)).
Form 1 will display the current time, text box for the count of clients with zero, a combo box for the 3 product categories, command button to view the corresponding form (form2, form 3 or form 4) to the product category
Form 2, 3, 4 to display the names of the clients with zero orders in that category and a command button to close the form.
There should not be any changes in the reference excel files after the execution of the file. These files are updated daily.
Also, is there a way to automatically open the main form when windows start?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 2, 2010 at 11:03 PM
Jun 2, 2010 at 11:03 PM
is it possible to post a sample book, First I dont have to reinvent the wheel and other it is easier to see the issue
here's the download link of one of the product category
http://wikisend.com/download/641338/Prodcategory1.xls
or forum link [URL=http://wikisend.com/download/641338/Prodcategory1.xls]Prodcategory1.xls[/URL]
here's a link to the screenshots of the forms
Download link : http://wikisend.com/download/587834/my screenshots.doc
Forum Link: [URL=http://wikisend.com/download/587834/my screenshots.doc]my screenshots.doc[/URL]
http://wikisend.com/download/641338/Prodcategory1.xls
or forum link [URL=http://wikisend.com/download/641338/Prodcategory1.xls]Prodcategory1.xls[/URL]
here's a link to the screenshots of the forms
Download link : http://wikisend.com/download/587834/my screenshots.doc
Forum Link: [URL=http://wikisend.com/download/587834/my screenshots.doc]my screenshots.doc[/URL]
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 3, 2010 at 10:26 AM
Jun 3, 2010 at 10:26 AM
Where is the form ?. In the same book there is no form
Yes. There is no form in that book because it is located in the other form. That is why I have provided the screen shot of the form so that you can visualize it. The book you have seen is the form which will be viewed if the "view sheet" command button is clicked, It is also the same sheet where the data of the product category chosen will be extracted to appear in the textbox (the count of Zeros) of the main form and where the Label/Listbox of the product category form can get data too. I can't provide you the exact file because it is very confidential. Please understand.
Remember, my only problem here is how to count the zeros under the current date then display the count in the textbox of the mainform AND display the names/clients which correspond in the zero values to the label/texbox of the chosen product category form.
I hope you can give me the code asap.
Thanks!
Remember, my only problem here is how to count the zeros under the current date then display the count in the textbox of the mainform AND display the names/clients which correspond in the zero values to the label/texbox of the chosen product category form.
I hope you can give me the code asap.
Thanks!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 5, 2010 at 08:14 AM
Jun 5, 2010 at 08:14 AM
I do not need exact file. I wanted to see the how things are connected like how form is using sheet etc You could have fake data.
For count, you can either put a count of zero on on the actual sheet and refer it, For that you can use countif
for the list of names, you just have to loop thru the names, and check if value under the right date is zero or not.
To find the right date column, you can use range method FIND.
For count, you can either put a count of zero on on the actual sheet and refer it, For that you can use countif
for the list of names, you just have to loop thru the names, and check if value under the right date is zero or not.
To find the right date column, you can use range method FIND.
