Creating a Report based on input from user..

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 Workbook
ABCDEFG
1Task #DateTotal # of ItemsTotal Hrs.Comments
2
3NewRevDel
410104/01/20101212:00Task comment of Mr. A. Task comment of Mr. C.
510204/01/20101101:30Task comment of Mr. A. Task comment of Mr. B.
620104/01/20101012:00Task comment of Mr. B.
730104/01/20101001:00Task comment of Mr. C.
810104/03/20101114:00Task comment of Mr. C.
910304/03/20101112:00Task comment of Mr. A.
1020204/03/20101114:00Task comment of Mr. B.
1110404/10/20101011:30Task comment of Mr. A.
1220104/10/20101013:00Task comment of Mr. B.
1330204/10/20101102:30Task comment of Mr. C.
1410204/20/20101100:30Task comment of Mr. A.
1510504/20/20104426:00Task comment of Mr. A. Task comment of Mr. B.
1630104/20/20101101:30Task comment of Mr. C.
1730304/20/20101225:00Task comment of Mr. C.
1810605/01/20101111:00Task comment of Mr. A.
1920205/01/20101112:00Task comment of Mr. C.
2020405/01/20101100:30Task comment of Mr. B.
2110405/02/20100112:00Task comment of Mr. A.
2210605/02/20101111:45Task comment of Mr. B.
2320405/02/20100113:00Task comment of Mr. B.
2430505/02/20100114:00Task comment of Mr. C.
2510705/05/20102002:45Task comment of Mr. A. Task comment of Mr. B.
2630605/05/20101000:30Task comment of Mr. C.
2710305/10/20101112:00Task comment of Mr. A.
2820505/10/20101113:00Task comment of Mr. B.
2930405/10/20101111:30Task comment of Mr. C.
Tasks
Excel 2007
Excel Workbook
ABCDEFG
1Project #DateNewRevDelTotal Hrs.Comments
2
310104/01/20101212:00Project comment of Mr. A. Project comment of Mr. C.
410204/01/20101101:30Project comment of Mr. A. Project comment of Mr. B.
520104/01/20101012:00Project comment of Mr. B.
630104/01/20101001:00Project comment of Mr. C.
710104/03/20101114:00Project comment of Mr. C.
810304/03/20101112:00Project comment of Mr. A.
920204/03/20101114:00Project comment of Mr. B.
1010404/10/20101011:30Project comment of Mr. A.
1120104/10/20101013:00Project comment of Mr. B.
1230204/10/20101102:30Project comment of Mr. C.
1310204/20/20101100:30Project comment of Mr. A.
1410504/20/20104426:00Project comment of Mr. A. Project comment of Mr. B.
1530104/20/20101101:30Project comment of Mr. C.
1630304/20/20101225:00Project comment of Mr. C.
1710605/01/20101111:00Project comment of Mr. A.
1820205/01/20101112:00Project comment of Mr. C.
1920405/01/20101100:30Project comment of Mr. B.
2010405/02/20100112:00Project comment of Mr. A.
2110605/02/20101111:45Project comment of Mr. B.
2220405/02/20100113:00Project comment of Mr. B.
2330505/02/20100114:00Project comment of Mr. C.
2410705/05/20102002:45Project comment of Mr. A. Project comment of Mr. B.
2530605/05/20101000:30Project comment of Mr. C.
2610305/10/20101112:00Project comment of Mr. A.
2720505/10/20101113:00Project comment of Mr. B.
2830405/10/20101111:30Project comment of Mr. C.
Projects
Excel 2007
Excel Workbook
ABCDEFG
1Job #DateNewRevDelTotal Hrs.Comments
2
310104/01/20101212:00Maintenance comment of Mr. A. Maintenance comment of Mr. C.
410204/01/20101101:30Maintenance comment of Mr. A. Maintenance comment of Mr. B.
520104/01/20101012:00Maintenance comment of Mr. B.
630104/01/20101001:00Maintenance comment of Mr. C.
710104/03/20101114:00Maintenance comment of Mr. C.
810304/03/20101112:00Maintenance comment of Mr. A.
920204/03/20101114:00Maintenance comment of Mr. B.
1010404/10/20101011:30Maintenance comment of Mr. A.
1120104/10/20101013:00Maintenance comment of Mr. B.
1230204/10/20101102:30Maintenance comment of Mr. C.
1310204/20/20101100:30Maintenance comment of Mr. A.
1410504/20/20104426:00Maintenance comment of Mr. A. Maintenance comment of Mr. B.
1530104/20/20101101:30Maintenance comment of Mr. C.
1630304/20/20101225:00Maintenance comment of Mr. C.
1710605/01/20101111:00Maintenance comment of Mr. A.
1820205/01/20101112:00Maintenance comment of Mr. C.
1920405/01/20101100:30Maintenance comment of Mr. B.
2010405/02/20100112:00Maintenance comment of Mr. A.
2110605/02/20101111:45Maintenance comment of Mr. B.
2220405/02/20100113:00Maintenance comment of Mr. B.
2330505/02/20100114:00Maintenance comment of Mr. C.
2410705/05/20102002:45Maintenance comment of Mr. A. Maintenance comment of Mr. B.
2530605/05/20101000:30Maintenance comment of Mr. C.
2610305/10/20101112:00Maintenance comment of Mr. A.
2720505/10/20101113:00Maintenance comment of Mr. B.
2830405/10/20101111:30Maintenance comment of Mr. C.
Maintenance
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 Workbook
ABCDEFG
1Task #DateTotal # of ItemsTotal Hrs.Comments
2
3NewRevDel
410104/01/20101212:00Task comment of Mr. A. Task comment of Mr. C.
510204/01/20101101:30Task comment of Mr. A. Task comment of Mr. B.
620104/01/20101012:00Task comment of Mr. B.
730104/01/20101001:00Task comment of Mr. C.
810104/03/20101114:00Task comment of Mr. C.
910304/03/20101112:00Task comment of Mr. A.
1020204/03/20101114:00Task comment of Mr. B.
1110404/10/20101011:30Task comment of Mr. A.
1220104/10/20101013:00Task comment of Mr. B.
1330204/10/20101102:30Task comment of Mr. C.
14
15Summary
16Total # of Tasks:8(This should be the count of unique numbers from Column A)
17Task #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)
21Total Hours23:30(This should be the total of Column F - h:mm format)
22
Sheet1
Excel 2007
Cell Formulas
RangeFormula
F18=SUM(C4:C13)
F19=SUM(D4:D13)
F20=SUM(E4:E13)
F21=SUM(F4:F13)
#VALUE!
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 Workbook
ABCDEFG
25Task #DateTotal # of ItemsTotal Hrs.Comments
26
27NewRevDel
2810204/01/20101101:30Task comment of Mr. A. Task comment of Mr. B.
2920104/01/20101012:00Task comment of Mr. B.
3020204/03/20101114:00Task comment of Mr. B.
3120104/10/20101013:00Task comment of Mr. B.
32
33Summary
34Total # of Tasks:3(This should be the unique values from Column A)
35Task #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)
39Total Hours10:30(This should be the total of Column F - h:mm format)
40
Sheet1
Excel 2007
Cell Formulas
RangeFormula
F36=SUM(C28:C31)
F37=SUM(D28:D31)
F38=SUM(E28:E31)
F39=SUM(F28:F31)
#VALUE!
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 ..... :)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

srinivasaru

New Member
Joined
May 22, 2010
Messages
19
Hi Excel wizards,

Can anyone help me with the summary portion for the problem in post #1.

Thanks in advance :)

Srinivas.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,927
Messages
5,834,404
Members
430,282
Latest member
MeredithD26

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
Top