Subtotals (Excel 2000)

Topic

New Reply

I have a spreadsheet with subtotals. Is there a formula/macro that can automatically insert a row above and below a subtotal. The placement of the subtotal line changes each month. A search on the subject did not find a match. Thank you.

Reply | Quote

Replies

Cindy, are multiple subtotals stored on one sheet in separate columns on different rows, or are they all on the same row, just different columns, or are they on different sheets? (gosh I hope you understand that…example below)

One Sheet, different columns, different rows:

SUBTOTAL 1
	SUBTOTAL 2
		SUBTOTAL 3

Reply | Quote

One Sheet, different columns, different rows.

Reply | Quote

In military terms: Roger. Could you show a simple example? Does your sheet actually say the word Subtotal? If so, I’m assuming it would look like this:

A	B	C	D	E	F
SUBTOTAL 1:	4500
		SUBTOTAL 2:	5000
				SUBTOTAL 3:	5500

In which case the macro will have to select not only the SUBTOTAL cell, but also the one next to it…

Reply | Quote

Your sample is exactly correct. It is a typical data subtotal. On the subtotal row, the name of the field is used and then the word ‘Total’. I had wanted to insert a blank row above and below each subtotal so it would stand out. Almost like a conditional format, but for data subtotals.

Reply | Quote

I understand…I’m just playing around with keeping the Find from looping back to the beginning (sorta like a circular reference) and pushing your subtotals to the bottom of the worksheet

Reply | Quote

Cindy,
Is there a set number of occurances of “Subtotals”, or does it vary?

Reply | Quote

It varies every time the data subtotals is run.

Reply | Quote