Solved

Help on spreadsheet Macros

Posted on 2011-09-05
6
215 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 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

18 Experts available now in Live!

Get 1:1 Help Now