Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum in first blank cell and a few more things...
Hello all :)
I had raised a question a few days ago, but I still haven't figured out a solution. I hope someone can help :) My column J is a list of dollar amounts. 14.10 25.00 36.00 (blank cell) (want subtotal of previous 3 and make bold) (blank cell) 65.00 23.00 59.65 (Blank cell) (want subtotal of previous 3 and make bold) (blank cell) I want to do three things: 1) sum each of the totals so that they appear in the 1st blanl cell after the grouping. 2) Bold the subtotal. 3) Have each of the summed for a grand total. This is a list that will have varying rows, so that last row with the grand total will always be different. Any macro that can do all of this for me? Previously, someone suggested creating a new column k, and enter a formula. That worked for the subtotals, but I was not able to get a grand total of the subs. Also suggested was to use Autosum, but I havent been able to determina a range that will work. Thanks again for all of your help! Any suggestions are vey much appreciated! Bleu |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum in first blank cell and a few more things...
A non-macro solution:
Have you considered the SUBTOTAL function? The format is =SUBTOTAL(9,A1:A10) (Note: the "9," is important.) This provides the subtotal of the range you specify- and here's the tricky bit- while ***ignoring*** cells in that range that also use the SUBTOTAL formula. So if your column looked like (starting in A1, for example) 14 25 36 subtotal formula blank 15 26 37 48 subtotal formula blank =SUBTOTAL(9,A1:A11) would return 201 because it disregards the formulas in cells A4 and A10. To make this a quicker process, with the cell pointer in A4 press the Sigma menu button, which automatically enters a SUM formula, and repeat for other subtotal cells. Then search for the text string SUM( and replace it with SUBTOTAL(9, It all comes down to: did you request a macro in your original post because you have to do this over a huge range of cells all day every day, or because you thought there was no other way? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum in first blank cell and a few more things...
Dave,
Thanks for the info. I didn't request a macro in the original post, but really thought it would be easier. I have 15 different spreadhseets that I will use this info on - macro would be one step. Each are updated weekly, and vary in length, so assigning formula to specific cells would mean a lot of work! I didn't even know if what I was trying to do was possible. Thanks again, Bleu |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum in first blank cell and a few more things...
It is possible, because anything you can do manually with a keyboard or
mouse you can automate with VBA. The tricky part for you will be how to tell Excel where to start a range and where to end a range. If your column has blank rows in it already, you can get the macro to start at the first non-blank cell, pick up the address, and step downward row by row until a blank row is encountered, and write an appropriate formula based on the current address. Do the rows have any other clues that indicate where a range starts and stops? If yes, then your code can use those as guidelines too. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank (empty) cell always equal to 0?? | Excel Discussion (Misc queries) | |||
How to get a formula result zero as blank cell | Excel Discussion (Misc queries) | |||
blank cell turns to 0 | New Users to Excel | |||
conditional formating for a blank cell | Excel Discussion (Misc queries) | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) |