Need an excel procedure to replace static totals with actual formulas

I am an excel novice. We have a reporting software that generates sales reports, which can be exported to excel. Problem is, the material number totals are all static, not actual formulas. if there were just a few I would put them in manually, but there are WAY too many to do that. I have attached a sample export showing 4 material numbers, the actual export has 500 material numbers. Can anybody think of a way to quickly replace these static subtotals with formulas? The sub-total function in excel won't work as the totals are broken up by Revenue and Backlog, so the cells are not contiguous.
thx experts..
Bob R
sample.xlsx
bobrossi56Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Will the words "Total", "Revenue" and "Backlog" be in the Total line or will they need to be inserted?
0
bobrossi56Author Commented:
The words total, revenue and backlog will appear in every row.
0
bobrossi56Author Commented:
Meaning what you see in the sample is what I get, nothing has been manually inserted into the sample file.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Martin LissOlder than dirtCommented:
Try this macro.

Sub Totals()
Dim lngLastRow As Long
Dim lngRow As Long
Dim curRevenue As Currency
Dim curBackLog As Currency

lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = 2 To lngLastRow
    If Cells(lngRow, 1) = "Total" Then
        Cells(lngRow, 3) = curRevenue
        lngRow = lngRow + 1
        Cells(lngRow, 3) = curBackLog
        curRevenue = 0
        curBackLog = 0
    Else
        If Cells(lngRow, 2) = "Revenue" Then
            curRevenue = curRevenue + Cells(lngRow, 3)
        Else
            curBackLog = curBackLog + Cells(lngRow, 3)
        End If
    End If
Next
End Sub

Open in new window

0
bobrossi56Author Commented:
I tried that macro and it ran without any errors, but I still have the same static totals where I need formulas.
0
Martin LissOlder than dirtCommented:
What is the mechanism that generates the un-totaled data? In other words, when do you want the totals updated?
0
bobrossi56Author Commented:
We have an app called JReport by JinfoNet software. It creates the report and that is what we export to Excel. Their software gurus tell me the export cannot export the report with formulas intact.
0
Martin LissOlder than dirtCommented:
What I attached is a way of doing it without formulas. Any time you get new data you can just run the macro. It could also be automated by triggering it when any of the dollar amounts are changed, by pressing a button, or in any one of several other ways.
0
bobrossi56Author Commented:
I see, yes, this would work with a button. So, the sample file only contained a JAN column, the actual report will contain the current 12 month calendar, plus 12 months in the past and 11 months in the future. And prob 2,500 rows. What would the macro have to look like to accommodate that need?
thx....Bob
0
Martin LissOlder than dirtCommented:
So ignoring the fact that I copied and pasted the data would it look like this (with more months)?
?
0
bobrossi56Author Commented:
exactly
0
bobrossi56Author Commented:
And in addition, the backlog numbers and totals will never be touched. They can remain static for reference only, just the revenue numbers will change
0
bobrossi56Author Commented:
Basically I give this sheet out to the sales guys, they use the backlog as a reference and they forecast sales numbers in the revenue rows. Then they give it back to me for SQL loading.
0
Martin LissOlder than dirtCommented:
OK, I've got to go out and I'll get back to you in a few hours.
0
bobrossi56Author Commented:
OK, please don't forget about me :-) I am very excited about this solution and greatly appreciate your help!!!
thanks...Bob
0
Martin LissOlder than dirtCommented:
Here you go. You can drag the button (actually a shape) anyplace you want it.
Q-28638064.xlsm
0
bobrossi56Author Commented:
Great...so this macro doesn't care how many columns or rows I have? it will always update the revenue rows no matter?
thx
0
bobrossi56Author Commented:
One more question Martin...how would I set up the macro to run anytime a revenue dollar amount is changed?
thx...Bob
0
Martin LissOlder than dirtCommented:
so this macro doesn't care how many columns or rows I have? it will always update the revenue rows no matter?
That's correct.

how would I set up the macro to run anytime a revenue dollar amount is changed?

Add this code to the sheet. It's a little crude in that it recalculates all the revenue totals when one is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range(UsedRange.Address)) Is Nothing Then
        If Cells(Target.Row, 2) = "Revenue" Then
            Application.EnableEvents = False
                RevenueTotals
            Application.EnableEvents = True
        End If
    End If
            
End Sub

Open in new window

0
bobrossi56Author Commented:
I added this code right under the other code, not part of the other macro, it is seen as a new macro...but when I change a revenue value nothing happens. Did I plop it in the wrong place?
thx...Bob
0
Martin LissOlder than dirtCommented:
In the workbook I posted, the RevenueTotals macro is in a code module (Module1). The new code should be in the sheet. Go to Visual Basics and double-click on Sheet1 which will open up the Code area for the sheet. Paste all 11 lines of the Worksheet_Change sub there.
0
bobrossi56Author Commented:
OK, I am an idiot....here is the file with the pasted code, I know I am doing something wrong.
thanks so much
28638064.xlsm
0
Martin LissOlder than dirtCommented:
No you have it correctly placed. If you change for example cell E6 to 1000 and then select any other cell, cell E10 will change to 11,000. In any case here's an update where just the section that contains the changed revenue cell is updated.
Q-28638064b.xlsm
0
bobrossi56Author Commented:
I wonder if an Excel setting is wrong on my excel, because I downloaded your update, changed cell E2 from $8,000.00 to $5,000.00 and then clicked into cell E6 and the total in cell E4 stays at $8,000.00, never changes to $5,000.00 until I click the button. Sorry to be such a bother, I just want to get this right cause its genius!!!!
thx...Bob
0
Martin LissOlder than dirtCommented:
I think I see what happened. There's a line of code that says Application.EnableEvents = False and one further along that says Application.EnableEvents = True. The value of EnableEvents determines if the code behind all events (like WorksheetChange) is executed or not, so what I assume happened is that it got set to False and some error occurred before it got set back to True. You can fix this by going to Visual Basic and bringing up the Immediate Window via ctrl+g. In that window type Application.EnableEvents = True and press return. Let me know if that fixes the problem.
0
bobrossi56Author Commented:
Yes...that did it. Last question...I will need to copy/paste the magic that makes this work into each report export to excel I do. I am not sure what I need to copy/paste. When I do Alt + F8 I see a macro called revenuetotals, but if I type alt+F11 I see other code in several VB Projects, sheet1, immediate, and other things. Not being a VBA guy, I have no idea what is what, or what to copy/paste to my other spreadsheets.
thx again...Bob
0
Martin LissOlder than dirtCommented:
When you say "each report export" are you talking about other workbooks? If so each one of them needs to have two things:

1.

The RevenueTotals macro in a code module (like Module1), and

2.

the Worksheet_Change code in whatever sheet contains the revenue figures. Note that if the other workbook already has code in that event, you can probably just paste my code into the existing sub at the top. If you like, make the changes to one of the other workbooks and attach it here or email it to me (you'll find my email address in my profile) so I can check your work.
0
bobrossi56Author Commented:
OK, I will do this tomorrow Martin. Each month I will do an excel export from JReport and I will need to copy/paste the above 2 listed pieces of code to make the totals work. I will do a fresh extract tomorrow, and copy/paste the 2 things in and see if it works. I will also attach it here for your review.
thx martin...have a good night.
-Bob
0
bobrossi56Author Commented:
Good morning Martin...Problem...I extracted the exact report to excel I will export each month, I followed your 2 step copy/paste process into this report and nothing works. Disclaimer...the file we were working with yesterday was a mock-up of the actual report, I suspect this is the issue? Attached is the exact report, but missing 500 rows of data, but the few rows I show are exactly how the report will look. What did I do wrong?
thx...Bob
ActualReport.xlsm
0
Martin LissOlder than dirtCommented:
The problem is that the ActualReport has the data in different columns then the mock-up and when you need to look at data in specific places that kind of difference is very important. I can fix it up for you, no problem, but let me ask you three things:
1) Will every workbook have the same first four columns?
2) You included my original "crude" version of the WorksheetChange code which updates all the revenue totals when any one of them is changed, rather than the version in my Q_28638064b workbook which updates only the total associated with the changed revenue. Which version do you want?
3) I don't see the button I added. Do you want it?
0
Martin LissOlder than dirtCommented:
I just noticed the "Total" column (Q). I assume you want that updated to reflect any change you make to one of the detail revenue numbers. True?
0
bobrossi56Author Commented:
Answers...
1. Yes, every export will have the same first 4 columns.
2. I want the version that does the macro anytime a revenue cell changes, no button needed.
3. No button
4. Yes, the extract totals to the right in Q, I can easily add that formula and drag it. The more difficult one is the GRAND total at the bottom for revenue (we don't ever care about backlog), anyway to do that grand total?

Again Martin, I know what you have done thus far is WAY above and beyond, and I really do appreciate it. I wish I could award you 100,000 points....
-Bob
0
Martin LissOlder than dirtCommented:
Thanks, I'll have it for you sometime today.
0
Martin LissOlder than dirtCommented:
Here's an updated workbook that I think does everything you want including the grand total. A few things to note:

I deleted Module1 and coded everything in the Worksheet_Change event so you'll only need to copy that one procedure.
I added a number of comments and constants to the code that hopefully will help you understand it better. Please look at the code and if there's anything you don't understand just ask
I think cell D1257 should say "Revenue"
Several cells like E1263 and Q1263 are formatted without decimal places.
I have to admit that I didn't test the totals very thoroughly so please do that and let me know if you find any problems.
Q-28638064c.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobrossi56Author Commented:
Thanks Martin...I look forward to combing through this tomorrow. I will let you know how it all goes, and I do appreciate your efforts, and putting everything into 1 event sure helps my copy and pasting...and comments are most helpful. I was stepping through the code today while watching the spreadsheet trying to learn what was going on.
Have a good evening...Bob
0
Martin LissOlder than dirtCommented:
Have you seen my article on debugging? Some things it talks about like Watch expressions can help you as you step through the code.
0
bobrossi56Author Commented:
I extracted a new report, copy/pasted the sheet code into the sheet and it works almost perfectly Martin. The only thing it's not doing right is the GRAND total at the bottom. Here is what is happening:
Customer Material No      Revenue
        1           12345                  5.00
                     TOTAL                 5.00


GRAND                                    10.00

It is adding up the column revenue amounts + the column sub total amounts, it should only be adding up the sub total amounts, can't double dip even if it looks like we sell more than we actually do :-)
thx Martin....Bob
0
Martin LissOlder than dirtCommented:
Sorry. In the Worksheet_Change  event, near the bottom you'll find these two lines

                    If Cells(lngRow, REVENUE_COL) = "Revenue" Then
                        ' Sum the revenue values. Val() returns zero when the value is a space.

Open in new window


Change line 1 as shown

                    If Cells(lngRow, REVENUE_COL) = "Revenue" And Cells(lngRow, MATERIAL_COL) = "Total" Then
                        ' Sum the revenue values. Val() returns zero when the value is a space.

Open in new window

0
bobrossi56Author Commented:
OK, that worked....but there is one more slice to this pie...if you scroll down through the file, you will see the names of 4 sales people in column A. Right now we only have 4 sales folks, but we may hire more. Anyway, when this file is exported from JREPORT to Excel, I will slice and dice the excel file into 4 separate files called salesperson1.xlsm, salesperson2.xlsm, etc. Then email it to those folks and they will fill in their forecast by month on the revenue lines, and only use the backlog numbers as a reference. When I get all 4 excel files back, I will append them into 1, make a few changes to get the file ready for SQL, then import it into SQL. So,,,,after I slice the report into 4, I need the grand totals to work at the bottom. I took a file with your code, which is working perfect...I delete all the rows after salesman CANAVAN, I do not delete the grey grand total footer, but when I change a revenue number the grand total in grey does not change. For some reason once I delete a bunch of rows the grey grand total at the bottom stops working. You will also notice that there is a sub-total after each salesperson change, which does not work, but its a moot point if I am going to slice up the report into 4 files.
thx Martin...we are "almost" there....Bob
0
Martin LissOlder than dirtCommented:
So before we go any further let me see if I understand the process.

1. Export from jReport to Excel

You export the file from jReport to Excel. Does it have a grand total line at the bottom at that point even if there revenue totals my not be there?

2. Send a sheet to each sales person

You mail each salesperson their part of the sheet. On the current sheet there are 4 sections in col A that say "Canavan". Does he/she get 4 sections or just one and if just one, why are there 4 sections. Do the Totals in rows 231 and 232 go with it. Do headings like rows 1 and 2 go into each salesperson's sheet?

3. Recombine and modify

When you get them back you make some changes for SQL. Can you give an example of what they are?
0
bobrossi56Author Commented:
1. JREPORT reads my 3 SQL tables (actual, backlog and forecast) using J2E Java technology and allows me to export the results to excel. See attached file JREPORT EXTRACT. Grand totals is in the report footer. All data exports to excel statically. That grand total is moot though as soon as I slice up the report by sales person.

2. That salesperson break is a JReport thing, Canavan gets ALL his customers despite any breaks JReport puts in. All headings go with each file to the respective salesperson. See attached sliced up files for the 4 sales people. I use their sub total footer line as their grand total. We might soon have 5 files if we hire another sales person.

3. The sales people look at their reports, which tells them what we have sold and invoiced thus far (actual revenue), what we have in backlog (orders taken but not started) and the forecast revenue. These examples do not show any forecast numbers yet because we do not have a 2015 forecast in yet, we need to get these 4 files working before sales people can add their forecasts.

Once I get all 4 files back, I remove all total and sub total lines, as well as the backlog rows, leaving me with a file only showing forecast numbers. I append the 4 files together, and add a column for forecast version, and its ready for SQL load. I need the forecast version because in any given year we might have loaded 4 different forecasts. JReport allows me to run the report and prompts me for which version of the forecast to use.

Hope this helps Martin and again, THANK YOU!!
greschner-file.xlsx
fasino-file.xlsx
cooke-file.xlsx
canavan-file.xlsx
JREPORT-EXTRACT.xlsx
0
bobrossi56Author Commented:
Martin has provided me by far the BEST service I have ever encountered. MANY thanks for his diligence and loyalty to my project.
0
Martin LissOlder than dirtCommented:
Thank you for the kind words and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.