Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Function - add formula to all blank rows
Hi:
You all probably know that when you use the subtotal function it adds a blank line with totals below the columns that you specify. Is there a way to get the blank lines to fill using the row above the blank cell? I can add a formula manually but I have over 50 spreadsheets so I hoped that there was a code that would add the contents of the row above if the cell is blank. Example: Columns - Customer Name SalesRep Total Sales$ no data appears in the Customer Name and Sales Rep - only Total Sales$ since that column holds the subtotals. I need a code that will say if the cell is blank after subtotal then add the contents of the row (cell) above. This will show me the data in each row once I expand the (+) to the left. Please help... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Function - add formula to all blank rows
Nikki wrote:
Hi: You all probably know that when you use the subtotal function it adds a blank line with totals below the columns that you specify. Is there a way to get the blank lines to fill using the row above the blank cell? I can add a formula manually but I have over 50 spreadsheets so I hoped that there was a code that would add the contents of the row above if the cell is blank. Example: Columns - Customer Name SalesRep Total Sales$ no data appears in the Customer Name and Sales Rep - only Total Sales$ since that column holds the subtotals. I need a code that will say if the cell is blank after subtotal then add the contents of the row (cell) above. This will show me the data in each row once I expand the (+) to the left. Please help... First, consider a PivotTable instead of SubTotals. Second, if you insist on SubTotals, after adding them, collapse the detail so only the SubTotal lines are shown. Select the first visible cell in one of the blank columns. If it is cell B23, add the formula "=B22" (without the quotes). This should give you the result you want. Copy that cell, then select all of the cells you want filled. Select Edit / Go To / Special / Visible Cells Only / OK / Edit / Paste and you are done. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SubTotal function to return number of rows returns only zero | Excel Worksheet Functions | |||
subtotal blank cells | Excel Discussion (Misc queries) | |||
Pivot Table - Blank SubTotal in Calculated Field | Excel Worksheet Functions | |||
formula to remove blank rows | Excel Discussion (Misc queries) | |||
Formula to hide blank rows | Excel Worksheet Functions |