Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

Reporting Start and End Dates

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
Rrave26
Asked:
Rrave26
  • 10
  • 9
1 Solution
 
NARANTHIRANCommented:
0
 
gowflowCommented:
yes sure could you please post a sample workbook and will take it from there ?
gowflow
0
 
Rrave26Author Commented:
Sorry for the delay here is a copy of the database.
TEST-IM-METRICS-TRACKING.xlsm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gowflowCommented:
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
 
Rrave26Author Commented:
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
 
gowflowCommented:
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
 
Rrave26Author Commented:
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
 
gowflowCommented:
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
 
Rrave26Author Commented:
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
 
gowflowCommented:
what version of excel you have?
gowflow
0
 
Rrave26Author Commented:
2010.
0
 
Rrave26Author Commented:
Office Standard 2010 32 bit.
0
 
gowflowCommented:
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
 
gowflowCommented:
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
 
gowflowCommented:
Any news  ?
gowflow
0
 
Rrave26Author Commented:
I'm trying it right now.  It's not registering. working with local DSS to register it.
0
 
gowflowCommented:
So did it work ?
gowflow
0
 
Rrave26Author Commented:
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
 
Rrave26Author Commented:
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
 
gowflowCommented:
Tks glad it worked somehow.
gowflow
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now