Solved

Reporting Start and End Dates

Posted on 2014-02-02
20
150 Views
Last Modified: 2014-02-27
I have a spreadsheet that I need to generate several reports from.  These reports are for the previous month.  What I would like to do is to be able to enter a start and end date to grab the data needed.  

Is this possible or is there a better way?
0
Comment
Question by:Rrave26
  • 10
  • 9
20 Comments
 
LVL 5

Expert Comment

by:NARANTHIRAN
ID: 39828834
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39830282
yes sure could you please post a sample workbook and will take it from there ?
gowflow
0
 

Author Comment

by:Rrave26
ID: 39859065
Sorry for the delay here is a copy of the database.
TEST-IM-METRICS-TRACKING.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39861092
You post a question on 2014-02-03 at 01:47:25
literally couple of hours later you have 2 Experts giving you a reply.

...
it take you 11 DAYS to reply with a file attached. Fine.

We open this file it has so many Sheets it is not even funny ! each one not similar to an other, furthermore in your original question you have no specifics as to what sheet you need to get  and first of all your question is so vague a broad

These reports are for the previous month.  What I would like to do is to be able to enter a start and end date to grab the data needed.  

If you are serious about this:

1) you would reply as fast as possible
2) What is the specific sheet you need us to examine and what you want to achieve. You need to provide specifics so we can give you a specific answer. Like what you have now and what is the output that you would like to have.

Regards
gowflow
0
 

Author Comment

by:Rrave26
ID: 39877759
Well, thank you for opening my eyes. My apologies for not responding faster.  Unfortunately the job and position I work doesn't allow me to check this as fast as I would like and this project takes a back seat when other Crisis break.  

As for my vagueness about the sheet, you are correct.  I wasn't clear and my apologies.  

With that said and if your still interested, The report I am looking for help with is the Team Metrics.  I complete entering the data for the month which is reported on the IM Raw Data sheet.  What I am trying to accomplish on the Team Metrics sheet is to report, monthly, on the calls lead, reconvene's, handoffs, and the time durations of each.   I have created formulas in each of these cells.  What I want to know is how can I enter a date range and then have all of the subsequent formulas updated with the date range?

Again, my apologies for not getting back sooner.  

Jeff
0
 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
ID: 39878347
Is that what you want ?

I created a calendar for you and when you click on J4 or K4 the calendar popsup and you can choose a date that will be updated in these fields. I modified all your formulas to point to these 2 field.

I noticed that you had the date strict like
>1/1/2014
<1/31/2014

I changed then to include the extreems
>=1/1/2014
<=1/31/2014

or whatever date you choose.
Let me know if this is what you want.
gowflow
TEST-IM-METRICS-TRACKING-V01.xlsm
0
 

Author Comment

by:Rrave26
ID: 39878619
This is exactly what I am looking for.  However, I am getting a compile error.  I think it can't find the calendar object that you used.  Can you give me the file name so I can activate it or download it?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39878864
ok then do this.
1) open excel blank new workbook not the file I posted and go to Excel options / Trust Center / Macro Settings / And make sure Disable All Macroes with notification is selected (the second item). then press ok and close Excel altogether.

2) Open the file I posted

3) You will see above the Address bar a button with OPTIONS DO NOT press on it (this should activate the macro at this stage we want to disable macro se we don't get the error)

4) Press on the Developper Tab and press on Visual Basic the first I con to the left.

5) You will see the VBA editor on the Tools menu select the first item Reference

6) You will see in the List MISSING Microsoft Calendar Control 2007 untick it. You probably have 2010 or 2013 but it is there. Go thru the list and try to see if you find this item under 2010 or 2013 or whatever version you have.

7) If you don't find it its fine will do this: close the VBA editor and back to the sheet on the developer tab You have under Control Section Click on Insert and in the bottom right corner there is a small button click on it and it will open a list scroll in the list till you find Calendar Control when found click on it and press OK

8) the icon with turn like a plus sign just click and draw the control on the size you want anywhere on the sheet.

9) YOu need to save the project close it and open it again.

10) Press on the option button to activate macros and here you go.

If any other problem let me know
gowflow
0
 

Author Comment

by:Rrave26
ID: 39879246
I followed your instructions and I don't have calendar control in either place.  Which means I need to download.  I had to do this once before and since I got a new machine I need to do it again, but now I have to wait for admin rights to download it.  With my IT group it could be days.  So, I'm not ignoring you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39879420
what version of excel you have?
gowflow
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Rrave26
ID: 39879626
2010.
0
 

Author Comment

by:Rrave26
ID: 39879630
Office Standard 2010 32 bit.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39879845
ok try putting this attached file MSCAL.txt file (change its extention to MSCAL.OCX after downloading it in Folder

C:\Program Files (x86)\Microsoft Office\Office14

If you know about regestring it do it else do the following

Press on START button and copy paste this
Regsvr32 "C:\Program Files (x86)\Microsoft Office\Office14\MSCAL.OCX" /u

then
Regsvr32 "C:\Program Files (x86)\Microsoft Office\Office14\MSCAL.OCX"

You should get regserver Registered successfully.

Try opening the file after that and look for Calendar as per previous instructions.
gowflow
MSCAL.txt
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39880252
I hope you were able to install the ocx and got this to work. I updated the file to make it easier to see results (make-up)

I removed the 0 showing and put dotted lines in between rows.

I kept the calendar, that is if you were able to make it work, if not let me know then there is a workaround.

gowflow
TEST-IM-METRICS-TRACKING-V02.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39885171
Any news  ?
gowflow
0
 

Author Comment

by:Rrave26
ID: 39888759
I'm trying it right now.  It's not registering. working with local DSS to register it.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39890041
So did it work ?
gowflow
0
 

Author Comment

by:Rrave26
ID: 39891953
The MSCAL.ocx file won't register.  Now I have the senior working on it.  I did get it work on my personal computer so I will close this out.  You've been patient enough.  i can get it to go from here.
0
 

Author Closing Comment

by:Rrave26
ID: 39891958
Gowflow's knowledge and patience were fantastic.  His solution was exactly what I was looking for. If I could change the points awarded here I would change it to 500 for all the work that was done.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39892037
Tks glad it worked somehow.
gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now