Solved

Help on spreadsheet Macros

Posted on 2011-09-05
6
224 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

803 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