Avatar of bobrossi56
bobrossi56
 asked on

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
Microsoft ExcelVBA

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Will the words "Total", "Revenue" and "Backlog" be in the Total line or will they need to be inserted?
bobrossi56

ASKER
The words total, revenue and backlog will appear in every row.
bobrossi56

ASKER
Meaning what you see in the sample is what I get, nothing has been manually inserted into the sample file.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Martin Liss

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

bobrossi56

ASKER
I tried that macro and it ran without any errors, but I still have the same static totals where I need formulas.
Martin Liss

What is the mechanism that generates the un-totaled data? In other words, when do you want the totals updated?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bobrossi56

ASKER
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.
Martin Liss

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.
bobrossi56

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Martin Liss

So ignoring the fact that I copied and pasted the data would it look like this (with more months)?
?
bobrossi56

ASKER
exactly
bobrossi56

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bobrossi56

ASKER
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.
Martin Liss

OK, I've got to go out and I'll get back to you in a few hours.
bobrossi56

ASKER
OK, please don't forget about me :-) I am very excited about this solution and greatly appreciate your help!!!
thanks...Bob
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

Here you go. You can drag the button (actually a shape) anyplace you want it.
Q-28638064.xlsm
bobrossi56

ASKER
Great...so this macro doesn't care how many columns or rows I have? it will always update the revenue rows no matter?
thx
bobrossi56

ASKER
One more question Martin...how would I set up the macro to run anytime a revenue dollar amount is changed?
thx...Bob
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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

bobrossi56

ASKER
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
Martin Liss

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
bobrossi56

ASKER
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
Martin Liss

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
bobrossi56

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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.
bobrossi56

ASKER
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
Martin Liss

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bobrossi56

ASKER
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
bobrossi56

ASKER
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
Martin Liss

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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?
bobrossi56

ASKER
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
Martin Liss

Thanks, I'll have it for you sometime today.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bobrossi56

ASKER
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
Martin Liss

Have you seen my article on debugging? Some things it talks about like Watch expressions can help you as you step through the code.
bobrossi56

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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

bobrossi56

ASKER
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
Martin Liss

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
bobrossi56

ASKER
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
bobrossi56

ASKER
Martin has provided me by far the BEST service I have ever encountered. MANY thanks for his diligence and loyalty to my project.
Martin Liss

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.