srinivasaru
New Member
- Joined
- May 22, 2010
- Messages
- 19
Hi Excel Gurus,
I am using excel 2007. I am displaying below three sample excel sheets. These excel sheets are consolidation of individual sheets using VBE.
Excel 2007
Excel 2007
Excel 2007
From this I want to prepare a report after getting input from the users and a summary below the report. The users will input 1 choice and 2 filtering conditions.
The choice will be like (All, Tasks, Projects, and Maintenance). If the user selects "Tasks", then data from the sheet named "Tasks" should be displayed and a summary of the datas should be displayed based on the filtering conditions. If the user selects "All", then data from all the sheets should be displayed in separate pages with the summary based on the filtering conditions.
The first filtering condition is between dates. The user will input "From Date" and "To Date".
The second filtering condition is any text from the comments column like "Mr. A", then the report should filter for only "Mr. A" from the comments column. This condition is an option, the user may input or may not.
For this, Is it okay to create an userform (I am somewhat okay with VBE, but never used forms) or is there any other way to get the input from the users.
The report should be displayed in the format as mentioned below:
1. The user input is
a. Choice selected is "Tasks".
b. Date selected is "From Date" 04/01/2010 and "To Date" 04/10/2010.
c. User has not mentioned any text to filter.
Excel 2007
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
2. The user input is
a. Choice selected is "Tasks".
b. Date selected is "From Date" 04/01/2010 and "To Date" 04/10/2010.
c. User has mentioned "Mr. B" as filtering condition.
Excel 2007
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Please help me to accomplish this task as I am pretty sure I will get a good advise from the excel gurus in this forum.
Thanks in advance .....
I am using excel 2007. I am displaying below three sample excel sheets. These excel sheets are consolidation of individual sheets using VBE.
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Task # | Date | Total # of Items | Total Hrs. | Comments | ||||
| 2 | |||||||||
| 3 | New | Rev | Del | ||||||
| 4 | 101 | 04/01/2010 | 1 | 2 | 1 | 2:00 | Task comment of Mr. A. Task comment of Mr. C. | ||
| 5 | 102 | 04/01/2010 | 1 | 1 | 0 | 1:30 | Task comment of Mr. A. Task comment of Mr. B. | ||
| 6 | 201 | 04/01/2010 | 1 | 0 | 1 | 2:00 | Task comment of Mr. B. | ||
| 7 | 301 | 04/01/2010 | 1 | 0 | 0 | 1:00 | Task comment of Mr. C. | ||
| 8 | 101 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Task comment of Mr. C. | ||
| 9 | 103 | 04/03/2010 | 1 | 1 | 1 | 2:00 | Task comment of Mr. A. | ||
| 10 | 202 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Task comment of Mr. B. | ||
| 11 | 104 | 04/10/2010 | 1 | 0 | 1 | 1:30 | Task comment of Mr. A. | ||
| 12 | 201 | 04/10/2010 | 1 | 0 | 1 | 3:00 | Task comment of Mr. B. | ||
| 13 | 302 | 04/10/2010 | 1 | 1 | 0 | 2:30 | Task comment of Mr. C. | ||
| 14 | 102 | 04/20/2010 | 1 | 1 | 0 | 0:30 | Task comment of Mr. A. | ||
| 15 | 105 | 04/20/2010 | 4 | 4 | 2 | 6:00 | Task comment of Mr. A. Task comment of Mr. B. | ||
| 16 | 301 | 04/20/2010 | 1 | 1 | 0 | 1:30 | Task comment of Mr. C. | ||
| 17 | 303 | 04/20/2010 | 1 | 2 | 2 | 5:00 | Task comment of Mr. C. | ||
| 18 | 106 | 05/01/2010 | 1 | 1 | 1 | 1:00 | Task comment of Mr. A. | ||
| 19 | 202 | 05/01/2010 | 1 | 1 | 1 | 2:00 | Task comment of Mr. C. | ||
| 20 | 204 | 05/01/2010 | 1 | 1 | 0 | 0:30 | Task comment of Mr. B. | ||
| 21 | 104 | 05/02/2010 | 0 | 1 | 1 | 2:00 | Task comment of Mr. A. | ||
| 22 | 106 | 05/02/2010 | 1 | 1 | 1 | 1:45 | Task comment of Mr. B. | ||
| 23 | 204 | 05/02/2010 | 0 | 1 | 1 | 3:00 | Task comment of Mr. B. | ||
| 24 | 305 | 05/02/2010 | 0 | 1 | 1 | 4:00 | Task comment of Mr. C. | ||
| 25 | 107 | 05/05/2010 | 2 | 0 | 0 | 2:45 | Task comment of Mr. A. Task comment of Mr. B. | ||
| 26 | 306 | 05/05/2010 | 1 | 0 | 0 | 0:30 | Task comment of Mr. C. | ||
| 27 | 103 | 05/10/2010 | 1 | 1 | 1 | 2:00 | Task comment of Mr. A. | ||
| 28 | 205 | 05/10/2010 | 1 | 1 | 1 | 3:00 | Task comment of Mr. B. | ||
| 29 | 304 | 05/10/2010 | 1 | 1 | 1 | 1:30 | Task comment of Mr. C. | ||
Tasks | |||||||||
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Project # | Date | New | Rev | Del | Total Hrs. | Comments | ||
| 2 | |||||||||
| 3 | 101 | 04/01/2010 | 1 | 2 | 1 | 2:00 | Project comment of Mr. A. Project comment of Mr. C. | ||
| 4 | 102 | 04/01/2010 | 1 | 1 | 0 | 1:30 | Project comment of Mr. A. Project comment of Mr. B. | ||
| 5 | 201 | 04/01/2010 | 1 | 0 | 1 | 2:00 | Project comment of Mr. B. | ||
| 6 | 301 | 04/01/2010 | 1 | 0 | 0 | 1:00 | Project comment of Mr. C. | ||
| 7 | 101 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Project comment of Mr. C. | ||
| 8 | 103 | 04/03/2010 | 1 | 1 | 1 | 2:00 | Project comment of Mr. A. | ||
| 9 | 202 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Project comment of Mr. B. | ||
| 10 | 104 | 04/10/2010 | 1 | 0 | 1 | 1:30 | Project comment of Mr. A. | ||
| 11 | 201 | 04/10/2010 | 1 | 0 | 1 | 3:00 | Project comment of Mr. B. | ||
| 12 | 302 | 04/10/2010 | 1 | 1 | 0 | 2:30 | Project comment of Mr. C. | ||
| 13 | 102 | 04/20/2010 | 1 | 1 | 0 | 0:30 | Project comment of Mr. A. | ||
| 14 | 105 | 04/20/2010 | 4 | 4 | 2 | 6:00 | Project comment of Mr. A. Project comment of Mr. B. | ||
| 15 | 301 | 04/20/2010 | 1 | 1 | 0 | 1:30 | Project comment of Mr. C. | ||
| 16 | 303 | 04/20/2010 | 1 | 2 | 2 | 5:00 | Project comment of Mr. C. | ||
| 17 | 106 | 05/01/2010 | 1 | 1 | 1 | 1:00 | Project comment of Mr. A. | ||
| 18 | 202 | 05/01/2010 | 1 | 1 | 1 | 2:00 | Project comment of Mr. C. | ||
| 19 | 204 | 05/01/2010 | 1 | 1 | 0 | 0:30 | Project comment of Mr. B. | ||
| 20 | 104 | 05/02/2010 | 0 | 1 | 1 | 2:00 | Project comment of Mr. A. | ||
| 21 | 106 | 05/02/2010 | 1 | 1 | 1 | 1:45 | Project comment of Mr. B. | ||
| 22 | 204 | 05/02/2010 | 0 | 1 | 1 | 3:00 | Project comment of Mr. B. | ||
| 23 | 305 | 05/02/2010 | 0 | 1 | 1 | 4:00 | Project comment of Mr. C. | ||
| 24 | 107 | 05/05/2010 | 2 | 0 | 0 | 2:45 | Project comment of Mr. A. Project comment of Mr. B. | ||
| 25 | 306 | 05/05/2010 | 1 | 0 | 0 | 0:30 | Project comment of Mr. C. | ||
| 26 | 103 | 05/10/2010 | 1 | 1 | 1 | 2:00 | Project comment of Mr. A. | ||
| 27 | 205 | 05/10/2010 | 1 | 1 | 1 | 3:00 | Project comment of Mr. B. | ||
| 28 | 304 | 05/10/2010 | 1 | 1 | 1 | 1:30 | Project comment of Mr. C. | ||
Projects | |||||||||
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Job # | Date | New | Rev | Del | Total Hrs. | Comments | ||
| 2 | |||||||||
| 3 | 101 | 04/01/2010 | 1 | 2 | 1 | 2:00 | Maintenance comment of Mr. A. Maintenance comment of Mr. C. | ||
| 4 | 102 | 04/01/2010 | 1 | 1 | 0 | 1:30 | Maintenance comment of Mr. A. Maintenance comment of Mr. B. | ||
| 5 | 201 | 04/01/2010 | 1 | 0 | 1 | 2:00 | Maintenance comment of Mr. B. | ||
| 6 | 301 | 04/01/2010 | 1 | 0 | 0 | 1:00 | Maintenance comment of Mr. C. | ||
| 7 | 101 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Maintenance comment of Mr. C. | ||
| 8 | 103 | 04/03/2010 | 1 | 1 | 1 | 2:00 | Maintenance comment of Mr. A. | ||
| 9 | 202 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Maintenance comment of Mr. B. | ||
| 10 | 104 | 04/10/2010 | 1 | 0 | 1 | 1:30 | Maintenance comment of Mr. A. | ||
| 11 | 201 | 04/10/2010 | 1 | 0 | 1 | 3:00 | Maintenance comment of Mr. B. | ||
| 12 | 302 | 04/10/2010 | 1 | 1 | 0 | 2:30 | Maintenance comment of Mr. C. | ||
| 13 | 102 | 04/20/2010 | 1 | 1 | 0 | 0:30 | Maintenance comment of Mr. A. | ||
| 14 | 105 | 04/20/2010 | 4 | 4 | 2 | 6:00 | Maintenance comment of Mr. A. Maintenance comment of Mr. B. | ||
| 15 | 301 | 04/20/2010 | 1 | 1 | 0 | 1:30 | Maintenance comment of Mr. C. | ||
| 16 | 303 | 04/20/2010 | 1 | 2 | 2 | 5:00 | Maintenance comment of Mr. C. | ||
| 17 | 106 | 05/01/2010 | 1 | 1 | 1 | 1:00 | Maintenance comment of Mr. A. | ||
| 18 | 202 | 05/01/2010 | 1 | 1 | 1 | 2:00 | Maintenance comment of Mr. C. | ||
| 19 | 204 | 05/01/2010 | 1 | 1 | 0 | 0:30 | Maintenance comment of Mr. B. | ||
| 20 | 104 | 05/02/2010 | 0 | 1 | 1 | 2:00 | Maintenance comment of Mr. A. | ||
| 21 | 106 | 05/02/2010 | 1 | 1 | 1 | 1:45 | Maintenance comment of Mr. B. | ||
| 22 | 204 | 05/02/2010 | 0 | 1 | 1 | 3:00 | Maintenance comment of Mr. B. | ||
| 23 | 305 | 05/02/2010 | 0 | 1 | 1 | 4:00 | Maintenance comment of Mr. C. | ||
| 24 | 107 | 05/05/2010 | 2 | 0 | 0 | 2:45 | Maintenance comment of Mr. A. Maintenance comment of Mr. B. | ||
| 25 | 306 | 05/05/2010 | 1 | 0 | 0 | 0:30 | Maintenance comment of Mr. C. | ||
| 26 | 103 | 05/10/2010 | 1 | 1 | 1 | 2:00 | Maintenance comment of Mr. A. | ||
| 27 | 205 | 05/10/2010 | 1 | 1 | 1 | 3:00 | Maintenance comment of Mr. B. | ||
| 28 | 304 | 05/10/2010 | 1 | 1 | 1 | 1:30 | Maintenance comment of Mr. C. | ||
Maintenance | |||||||||
From this I want to prepare a report after getting input from the users and a summary below the report. The users will input 1 choice and 2 filtering conditions.
The choice will be like (All, Tasks, Projects, and Maintenance). If the user selects "Tasks", then data from the sheet named "Tasks" should be displayed and a summary of the datas should be displayed based on the filtering conditions. If the user selects "All", then data from all the sheets should be displayed in separate pages with the summary based on the filtering conditions.
The first filtering condition is between dates. The user will input "From Date" and "To Date".
The second filtering condition is any text from the comments column like "Mr. A", then the report should filter for only "Mr. A" from the comments column. This condition is an option, the user may input or may not.
For this, Is it okay to create an userform (I am somewhat okay with VBE, but never used forms) or is there any other way to get the input from the users.
The report should be displayed in the format as mentioned below:
1. The user input is
a. Choice selected is "Tasks".
b. Date selected is "From Date" 04/01/2010 and "To Date" 04/10/2010.
c. User has not mentioned any text to filter.
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Task # | Date | Total # of Items | Total Hrs. | Comments | ||||
| 2 | |||||||||
| 3 | New | Rev | Del | ||||||
| 4 | 101 | 04/01/2010 | 1 | 2 | 1 | 2:00 | Task comment of Mr. A. Task comment of Mr. C. | ||
| 5 | 102 | 04/01/2010 | 1 | 1 | 0 | 1:30 | Task comment of Mr. A. Task comment of Mr. B. | ||
| 6 | 201 | 04/01/2010 | 1 | 0 | 1 | 2:00 | Task comment of Mr. B. | ||
| 7 | 301 | 04/01/2010 | 1 | 0 | 0 | 1:00 | Task comment of Mr. C. | ||
| 8 | 101 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Task comment of Mr. C. | ||
| 9 | 103 | 04/03/2010 | 1 | 1 | 1 | 2:00 | Task comment of Mr. A. | ||
| 10 | 202 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Task comment of Mr. B. | ||
| 11 | 104 | 04/10/2010 | 1 | 0 | 1 | 1:30 | Task comment of Mr. A. | ||
| 12 | 201 | 04/10/2010 | 1 | 0 | 1 | 3:00 | Task comment of Mr. B. | ||
| 13 | 302 | 04/10/2010 | 1 | 1 | 0 | 2:30 | Task comment of Mr. C. | ||
| 14 | |||||||||
| 15 | Summary | ||||||||
| 16 | Total # of Tasks: | 8 | (This should be the count of unique numbers from Column A) | ||||||
| 17 | Task #s: | 101, 102, 201, 301, 103, 202, 104, 302 | (This should be the unique numbers from Column A) | ||||||
| 18 | # of New Items: | 10 | (This should be the total of Column C) | ||||||
| 19 | # of Revised Items: | 7 | (This should be the total of Column D) | ||||||
| 20 | # of Deleted Items: | 7 | (This should be the total of Column E) | ||||||
| 21 | Total Hours | 23:30 | (This should be the total of Column F - h:mm format) | ||||||
| 22 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F18 | =SUM(C4:C13) | |
| F19 | =SUM(D4:D13) | |
| F20 | =SUM(E4:E13) | |
| F21 | =SUM(F4:F13) | |
#VALUE!
2. The user input is
a. Choice selected is "Tasks".
b. Date selected is "From Date" 04/01/2010 and "To Date" 04/10/2010.
c. User has mentioned "Mr. B" as filtering condition.
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 25 | Task # | Date | Total # of Items | Total Hrs. | Comments | ||||
| 26 | |||||||||
| 27 | New | Rev | Del | ||||||
| 28 | 102 | 04/01/2010 | 1 | 1 | 0 | 1:30 | Task comment of Mr. A. Task comment of Mr. B. | ||
| 29 | 201 | 04/01/2010 | 1 | 0 | 1 | 2:00 | Task comment of Mr. B. | ||
| 30 | 202 | 04/03/2010 | 1 | 1 | 1 | 4:00 | Task comment of Mr. B. | ||
| 31 | 201 | 04/10/2010 | 1 | 0 | 1 | 3:00 | Task comment of Mr. B. | ||
| 32 | |||||||||
| 33 | Summary | ||||||||
| 34 | Total # of Tasks: | 3 | (This should be the unique values from Column A) | ||||||
| 35 | Task #s: | 101, 201, 202 | (This should be the unique numbers from Column A) | ||||||
| 36 | # of New Items: | 4 | (This should be the total of Column C) | ||||||
| 37 | # of Revised Items: | 2 | (This should be the total of Column D) | ||||||
| 38 | # of Deleted Items: | 3 | (This should be the total of Column E) | ||||||
| 39 | Total Hours | 10:30 | (This should be the total of Column F - h:mm format) | ||||||
| 40 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F36 | =SUM(C28:C31) | |
| F37 | =SUM(D28:D31) | |
| F38 | =SUM(E28:E31) | |
| F39 | =SUM(F28:F31) | |
#VALUE!
Please help me to accomplish this task as I am pretty sure I will get a good advise from the excel gurus in this forum.
Thanks in advance .....
