Hi, I have a excel spreadsheet with a lot of codes per division. I want a mocro to add a subtotal at each change in the codes and then it must fill the division name down so that I can only extract the subtotal info but still can see for wich division it is.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Fill cells down with macro
- Thread starter Riaang
- Start date
The spreadsheet displays the ageing on a member's account (90days, 60days, 30days, current and Total) it starts from column E to J and the paypoint is in column L. I want subtotals after the ageing at each change in L. And then column L must be filled down to bring in the paypoint detail next to each subtotal.
This message was edited by Riaang on 2002-08-31 07:29
This message was edited by Riaang on 2002-09-03 11:52
This message was edited by Riaang on 2002-08-31 07:29
This message was edited by Riaang on 2002-09-03 11:52
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
Have you tried a pivot table?
Upvote
0
purple_103
New Member
- Joined
- Nov 5, 2003
- Messages
- 1
I had exactly the same problem, but figured a quick way of doing it. I hope this helps!
If you do the subtotals command as usual (sort by the relevant column etc, & apply the subtotals command), you then have in each row of subtotals, in the column that you sorted by (say it was the name of a salesperson) something like "Joe Bloggs Total".
You can then apply a custom filter using criteria "contains - total". This will bring together all the rows with the subtotals in (ignore the fact that the formulas in the subtotaled rows now all display 0 - this will go back to the numbers it should be when the filter is removed).
These can now all be formatted without formatting the unseen rows inbetween.
In order to also display the extra info you need (assuming that it is either the same for each subtotaled group, or is in a consistent location which can be easily cell referenced) you can simply type in where you want the info " =cellref " where cellref is the cell reference of the info you need.
I hope this kinda makes sense as it is fairly difficult to explain without showing it to you!
If you do the subtotals command as usual (sort by the relevant column etc, & apply the subtotals command), you then have in each row of subtotals, in the column that you sorted by (say it was the name of a salesperson) something like "Joe Bloggs Total".
You can then apply a custom filter using criteria "contains - total". This will bring together all the rows with the subtotals in (ignore the fact that the formulas in the subtotaled rows now all display 0 - this will go back to the numbers it should be when the filter is removed).
These can now all be formatted without formatting the unseen rows inbetween.
In order to also display the extra info you need (assuming that it is either the same for each subtotaled group, or is in a consistent location which can be easily cell referenced) you can simply type in where you want the info " =cellref " where cellref is the cell reference of the info you need.
I hope this kinda makes sense as it is fairly difficult to explain without showing it to you!
Upvote
0
