Subtotal Function - add formula to all blank rows


N

Nikki

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

Advertisements

G

Glenn

Nikki said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top