Formulas in subtotal worksheets?

[Prospector]

I have a long worksheet (34000+ rows) in which I have grouped data by a stock
number. In the column following the stock number is the name of the item to
which the stock number refers. When I subtotal the worksheet and go to level
two (subtotals only), the name disappears. It's easy enough to create a
formula to copy the item name from the row above the subtotal; but then, is
there an easy way to copy that formula to each of the subtotal rows (about
8000-10000) without having to paste to each cell individually? I tried
pasting to the entire column, but that causes the formula to be posted to the
hidden cells as well, and results in the same item name being in every cell
in the column.

 

[Pete_UK]

Assuming you have something like this:

xxx name1 other data across
xxx name1
Subtotal for xxx
yyy name2
yyy name2
Subtotal for yyy

and you want name1 to appear in the cell next to "Subtotal for xxx",
and so on ...

Insert a new column C (temporarily). Click on the first cell where you
want the name to appear. Press F5 (or Edit | GoTo), then click Special
and then click Current Region. With the cells highlighed, press F5
again, click Special and then click Blanks. Now, type = in the cell
where the cursor is, click on the cell immediately above it and then
do CTRL-ENTER. The blank cells in column B will now all be filled with
the formula =(the cell above), so your names will appear how you
wanted them - you can fix the values in this column if you want.

Delete the temporary column C and then carry on as before.

Hope this helps.

Pete

 

[Prospector]

Thanks, Pete!!

Assuming you have something like this:

xxx name1 other data across
xxx name1
Subtotal for xxx
yyy name2
yyy name2
Subtotal for yyy

and you want name1 to appear in the cell next to "Subtotal for xxx",
and so on ...

Insert a new column C (temporarily). Click on the first cell where you
want the name to appear. Press F5 (or Edit | GoTo), then click Special
and then click Current Region. With the cells highlighed, press F5
again, click Special and then click Blanks. Now, type = in the cell
where the cursor is, click on the cell immediately above it and then
do CTRL-ENTER. The blank cells in column B will now all be filled with
the formula =(the cell above), so your names will appear how you
wanted them - you can fix the values in this column if you want.

Delete the temporary column C and then carry on as before.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks, Pete!!










- Show quoted text -