Subtotal Lines

**redmalva** · 07-08-2009, 04:05 PM

Hi there,

I have created subtotal lines for my data, but there is also non-numerical data in other columns that I would like to bring down automatically to the subtotal line. I would just copy and past, but I have a lot of subtotal lines.

Thanks in advance!

**JBeaucaire** · 07-08-2009, 06:09 PM

Can you mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data. Make sure the sample truly represents your actual data so any solution offered will work immediately.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

**redmalva** · 07-09-2009, 11:35 AM

Thank you for helping me.

I have attached a sample of the data that I am working with.

Bascially, I use the subtotal feature to get the attendance number for the particular title. However, I was looking for a way that Excel might bring down whether the title is "indie" or not and the name of the distributor. The results I want are highlighted in yellow.

I have about 200 separate titles and going through each one auditing and copying the distributor is time consuming.

Thanks in advance!

**JBeaucaire** · 07-09-2009, 01:55 PM

Since there is no other information to sift through, this report appears to be oneup, so you can simply do an =E10 in cell E11, right?

You're already manually setting the bottom of the dataset and manually entering a subtotal formula, since the chart only covers one film, there is nothing to work around and you can simply grab the value of the row above as easily as anything else.

**redmalva** · 07-09-2009, 05:58 PM

Hi,

There is actually more than one film. I use the sort function to organize by film title and then I use the subtotal menu feature to automatically give me subtotals of audience numbers based on the film titles.

At the end, I get groupings of subtotal lines with the films that I can collapse and uncollapse. About 200 separate groups. With the oneup, I would have to go to each subtotal line and manually enter the one up 200 times...

Thanks in advance!

**JBeaucaire** · 07-10-2009, 01:43 AM

1) Complete your list
2) Highlight columns C and E at the same time
3) Press Ctrl-1 to open the Cell Format box, set the Number format to GENERAL
4) With C and E still highlighted, press F5
5) Select SPECIAL
6) Select BLANKS > OK
7) Press the equal sign: =
8) Press the UP arrow
9) Press CTRL-ENTER

Now, all blank cells in C and E will have a formula in them to grab the value from the cell above.

**redmalva** · 07-11-2009, 01:55 AM

Thank you for this trick! It has help immensely!!

**JBeaucaire** · 07-11-2009, 04:44 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)