• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Help on spreadsheet Macros

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
jmkimberlain
Asked:
jmkimberlain
  • 4
  • 2
1 Solution
 
jmkimberlainAuthor Commented:
I can email the sheet.
0
 
dlmilleCommented:
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
 
jmkimberlainAuthor Commented:
Thanks a LOT!  The spreadsheet is attached.  I believe I took all the names out of it. Daily-Performance.xlsx
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
dlmilleCommented:
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
 
jmkimberlainAuthor Commented:
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
 
jmkimberlainAuthor Commented:
Great help with coding!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now