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 ..... :)
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,950
Messages
5,514,356
Members
408,999
Latest member
Virgothinker

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top