Solved

Help on spreadsheet Macros

Posted on 2011-09-05
6
206 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 41

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 41

Accepted Solution

by:
dlmille earned 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 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

14 Experts available now in Live!

Get 1:1 Help Now