Improve company productivity with a Business Account.Sign Up

x
?
Solved

Help on spreadsheet Macros

Posted on 2011-09-05
6
Medium Priority
?
253 Views
Last Modified: 2012-05-12
Would anyone be interested in helping with a spreadsheet?  I have a daily plant report that is filled out on one sheet.  I need a summary sheet.  The trick is that the summary sheets needs a date picker and then the report has to have whatever days totals picked and the MTD totals.  I need to find the date row and then copy some data and then sum other data. I'm stumped.
0
Comment
Question by:jmkimberlain
  • 4
  • 2
6 Comments
 

Author Comment

by:jmkimberlain
ID: 36486498
I can email the sheet.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36486578
Sure, post your spreadsheet (be sure you're not sharing confidental or sensitive data), and I and potentially other E-E experts will lend a hand.

Dave
0
 

Author Comment

by:jmkimberlain
ID: 36489836
Thanks a LOT!  The spreadsheet is attached.  I believe I took all the names out of it. Daily-Performance.xlsx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36492886
Ok.  I made a bit of progress -and perhaps you can take it from here.

First, I created a field that the datepicker could point too (go to design mode on the developer's ribbon, then rt-click on date picker and set the LINKED CELL to something off report - I used Range O8).  I then had to convert that to a real date (go figure) in Range O7, then created a date (to get the MTD) for the first of the month in cell  O5.  To make things easier, I created some helper columns below this in range N9:T22).  These help the VLOOKUP know what columns to use for the "today" - single date, part of the report.  Note, I don't know the diff in your database of beginning/ending inventory, so you'll need to sort that.  The data didn't make sense.  I also couldn't discern REFUSE from your report.

However, I then used VLOOKUP for the Daily elements - e.g.,

=VLOOKUP($O$7,'Daily Tonnage Data Input'!$A$8:$X$1000,O10,0) - $O$7 - the date, $A$8:$X1000 - the database to vlookup from, O10 - the column to pick (see off report to the right for column, 0 - exact match.  Note, you can wrap an =IfError( vlookup statement, "") to show blank instead of #VALUE! if the date doesn't exist in the database

For the MTD column, I had to resort to SUMIFS statement (an array SUMIF - summing based on criteria) - e.g.,

=SUMIFS('Daily Tonnage Data Input'!B$8:B$1000,'Daily Tonnage Data Input'!$A$8:$A$1000,">="&$O$5,'Daily Tonnage Data Input'!$A$8:$A$1000,"<="&$O$7)

B$8:B$1000 - that's the first column - Raw tons - would be C range for Clean tons, etc., this for Low Sulfur - move over columns for other fields across and down the list!
$A$9:$A$1000 >= start of month at $O$5
$A$8:$A$1000 <= current date picked at $O$7

Pretty straight forward?

Let me know if additional assistance is required.

See attached where all this is put in place.

Enjoy!

Dave
Daily-Performance-r1.xlsx
0
 

Author Comment

by:jmkimberlain
ID: 36492992
Thanks Dave, that puts me way ahead!  I really appreciate you taking the time.  I think I can take it from there, I've got a lot to learn.
0
 

Author Closing Comment

by:jmkimberlain
ID: 36492997
Great help with coding!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

608 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