Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
In-Line Subtotal Madness
I have been working my way through a project that checks for a travel
bill reconsilliation workbook (Without using filesearch, thanks to Myrna Larson, Bob Phillips and Ron De Bruin, amongst others) on the user's desktop, makes a backup copy, and reformats it into a CMS Invoice import file. The file conversion process has four steps, but the second one is the only one I can't figure out how to automate: A. The first step merges multiple spreadsheets together (Thank you, David McRitchie for your massive guides), inserts a custom key/error field (using my own custom TravelBillAnalysis function, the key merges values from columns A,G,and J) and resorts the data based on the KEY field values. The objective of this is to make all records flagged as errors bubble up to the top. (Using Chip Pearson's delightful ExtractElement function) B. The second step hides the error rows and subtotals the AMOUNT field based on changes in the KEY field. I need a subtotal of both where the AMOUNT field equals the key field, but I also need to subtotal Fees WITHIN those subtotals, where column 4 contains a given string "Fees" or begins with "ARC". This much I am able to do with Excel's subtotal tool and some complex formulas. THE BIG PROBLEM! ================ The end-users demand the data appears in a certain format because they must still manually modify it after this second step. I have to add two columns in the middle of the data set, one for FEES and one for the transaction TOTAL. I then have to move the subtotal information from the inserted subtotal rows to the appropriate column in-line with the last detail line before the subtotal row. Then I have to delete both the empty subtotal rows and all other rows besides the last one in each subtotal set where we want to move our subtotal information. (Does that make sense?!) This In-line subtotal requirement is the part that is killing me. I've tried numerous combinations of formulas, subtotals, subsorts, etc etc but I can't it to work. I've tried reversing the process to copy down the data into the subtotal line using James Cone's "FillInSubTotalBlanks()" subroutine, but then I can't figure out how to target the correct lines to delete. http://groups-beta.google.com/group/...7f161bbeae5260 Since the amount of data in the file varies each month, I have to keep all of the reference ranges flexible, but subroutines I've found or written for activecell movement and range selection get messed up when the subtotal lines are created while the auto filter is engaged. Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
In-Line Subtotal Madness
Actually, has anyone ever seen/created a module to replace the Subtotal
tool with an ENHANCED version? By enhanced I mean, it allows you to indicate WHERE the subtotals will appear (below, above, to the left, to the right, in an offset location, etc). That would accomplish what I'm trying to do. Just a thought. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
Copy Cell From Subtotal Line To Detail Line | Excel Discussion (Misc queries) | |||
ON SUBTOTAL LINE CARRY FORWARD DENOMINATOR FROM PREVIOUS LINE | Excel Worksheet Functions | |||
How do you add a blank line automatically after the Subtotal line | Excel Worksheet Functions | |||
Subtotal line selection | Excel Discussion (Misc queries) |